Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Ok. Since you can add a new stored procedure, I would suggest packaging up all the values, and shipping it down as XML to your stored procedure.</p> <p>You can find a kinda example here: <a href="http://granadacoder.wordpress.com/2009/01/27/bulk-insert-example-using-an-idatareader-to-strong-dataset-to-sql-server-xml/" rel="nofollow">http://granadacoder.wordpress.com/2009/01/27/bulk-insert-example-using-an-idatareader-to-strong-dataset-to-sql-server-xml/</a></p> <p>The good news, that example I have is older and coded to Sql Server 2000 (with OPENXML).</p> <p>..</p> <p>This would be better than sending down 300 parameters to a stored procedure, IMHO.</p> <p>The other advantage, if you have more than 1 row of data, you can ship that down as well.</p> <p>......</p> <p>The "gist" of it:</p> <p>First, you can get the 2000 "pubs" database here:</p> <p><a href="http://www.microsoft.com/en-us/download/details.aspx?id=23654" rel="nofollow">http://www.microsoft.com/en-us/download/details.aspx?id=23654</a></p> <p>Now add this stored procedure:</p> <p>/* USP */</p> <pre><code>DROP PROCEDURE dbo.uspTitleUpsert GO CREATE PROCEDURE dbo.uspTitleUpsert ( @xml_doc TEXT , @numberRowsAffected int output --return ) AS SET NOCOUNT ON DECLARE @hdoc INT -- handle to XML doc DECLARE @errorTracker int -- used to "remember" the @@ERROR DECLARE @updateRowCount int DECLARE @insertRowCount int --Create an internal representation of the XML document. EXEC sp_xml_preparedocument @hdoc OUTPUT, @XML_Doc -- build a table (variable table) to store the xml-based result set DECLARE @titleupdate TABLE ( identityid int IDENTITY (1,1) , title_id varchar(6) , title varchar(80) , type varchar(32) , pub_id varchar(32) , price money , advance money , royalty varchar(32) , ytd_sales varchar(32) , notes TEXT , pubdate datetime ) --the next call will take the info IN the @hdoc(with is the holder for @xml_doc), and put it IN a variableTable INSERT @titleupdate ( title_id , title , type , pub_id , price , advance , royalty , ytd_sales , notes , pubdate ) SELECT title_id , title , type , pub_id , price , advance , royalty , ytd_sales , notes , getdate() /*dbo.udf_convert_xml_date_to_datetime (pubdate)*/ FROM -- use the correct XPath .. the second arg ("2" here) distinquishes -- between textnode or an attribute, most times with --.NET typed datasets, its a "2" --This xpath MUST match the syntax of the DataSet OPENXML (@hdoc, '/TitlesDS/Titles', 2) WITH ( title_id varchar(6) , title varchar(80) , type varchar(32) , pub_id varchar(32) , price money , advance money , royalty varchar(32) , ytd_sales varchar(32) , notes TEXT , pubdate varchar(32) ) EXEC sp_xml_removedocument @hdoc select * from @titleupdate SET NOCOUNT OFF Update dbo.titles set title = vart.title , type = vart.type , pub_id = vart.pub_id , price = vart.price , advance = vart.advance , royalty = vart.royalty , ytd_sales = vart.ytd_sales , notes = vart.notes , pubdate = vart.pubdate FROM @titleupdate vart , dbo.titles realTable WHERE (rtrim(upper(realTable.title_id))) = ltrim(rtrim(upper(vart.title_id))) and exists ( select null from dbo.titles innerRealTable where (rtrim(upper(innerRealTable.title_id))) = ltrim(rtrim(upper(vart.title_id))) ) Select @updateRowCount = @@ROWCOUNT INSERT INTO dbo.titles ( title_id , title , type , pub_id , price , advance , royalty , ytd_sales , notes , pubdate ) Select title_id , title , type , pub_id , price , advance , royalty , ytd_sales , notes , pubdate FROM @titleupdate tu WHERE not exists ( select null from dbo.titles innerRealTable where (rtrim(upper(innerRealTable.title_id))) = ltrim(rtrim(upper(tu.title_id))) ) Select @insertRowCount = @@ROWCOUNT print '/@insertRowCount/' select @insertRowCount print '' print '/@updateRowCount/' select @updateRowCount print '' select @numberRowsAffected = @insertRowCount + @updateRowCount --select * from titles SET NOCOUNT OFF GO --GRANT EXECUTE on dbo.uspTitleUpsert TO pubsuser GO </code></pre> <p>/* Example usage */</p> <pre><code>EXEC dbo.uspTitleUpsert ' &lt;TitlesDS&gt; &lt;Titles&gt; &lt;title_id&gt;PN3333&lt;/title_id&gt; &lt;title&gt;Peanut Cooking&lt;/title&gt; &lt;type&gt;trad_cook&lt;/type&gt; &lt;pub_id&gt;0877&lt;/pub_id&gt; &lt;price&gt;3.33&lt;/price&gt; &lt;advance&gt;4444.00&lt;/advance&gt; &lt;royalty&gt;1&lt;/royalty&gt; &lt;ytd_sales&gt;33&lt;/ytd_sales&gt; &lt;notes&gt;Peanut Cooking Notes&lt;/notes&gt; &lt;pubdate&gt;&lt;/pubdate&gt; &lt;/Titles&gt; &lt;Titles&gt; &lt;title_id&gt;SSMS4444&lt;/title_id&gt; &lt;title&gt;Sql Server Management Studio&lt;/title&gt; &lt;type&gt;programming&lt;/type&gt; &lt;pub_id&gt;0877&lt;/pub_id&gt; &lt;price&gt;13.33&lt;/price&gt; &lt;advance&gt;5444.00&lt;/advance&gt; &lt;royalty&gt;2&lt;/royalty&gt; &lt;ytd_sales&gt;33&lt;/ytd_sales&gt; &lt;notes&gt;Sql Server Management Studio Notes&lt;/notes&gt; &lt;pubdate&gt;&lt;/pubdate&gt; &lt;/Titles&gt; &lt;/TitlesDS&gt; ' , 0 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POUpdate big table (a lot of columns). C# .NET
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USgranadaCoder
    1. USgranadaCoder
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COthanks a lot - I will analyze this possibility and will write here my result. Once again thanks at least for that this (OPENXML) still is unknown area for me. Is it fully compatible with SQL Server 2000?
      singulars
      1. PO
      1. USALZ
    2. COYeah, OPENXML is "baked in" to the TSQL functionality with Sql Server 2000. 2005 and up replaces OPENXML with some slightly different syntax, but they retain OPENXML for backwards compatibility. Bottomline, OPENXML in Sql Server 2000 is not voo doo, it is there for your usage.
      singulars
      1. PO
      1. USgranadaCoder
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload