Fog Creek Software
Discussion Board




Welcome! and rules

Joel on Software

Multiple INSERTs in one roundtrip

I am looking for a way to do multiple table inserts via one
roundtrip to the database (C#, SQL 2000). To make it more
concrete, let's say I have a People table with two fields,
Name and Age, and have 5 new persons that I need to add
to it. I do _not_ want to call a stored procedure 5 times
from my C# code; I want one trip to the database.

I see a few possible ways to approach this.

1.  Have a stored proc that takes 2 varchars: a
comma-separated list of names and a comma-separated list
of ages, parses them, and does multiple inserts.  Sounds
ugly and inefficient.

2. Put a limit on how many inserts I can make at a time
(say 5) and have a stored proc that takes 10 argumants: 5
names and 5 ages, some of which can be nulls. This is
better in that there's no string manipulation on the DB side,
but it's still far from being elegant.

3. Make use of the new Table data type that SQL 2000 has.
However, I am not sure how to use it from C#; there's no
such type in SqlDataType.

4. Is there such a thing in SQL 2000 as stored proc with a
variable number opf arguments? If there is, which I doubt,
I might be able to leverage it here.


I cannot imagine I am the first one to be facing this issue,
so I hope someone reading this knows of a way to tackle
this. 

Thanks in advance for any help.

- Mike.

Mike
Friday, June 25, 2004

..... call the stored procedure 5 times.

The bulk of the DB overhead is in establishing a connection & begin/commit for transactions.  Just don't do *that* 5 times and you'll be fine.

Koz
Friday, June 25, 2004

I basically just create a connection, create a transaction, loop over the sproc, commit the transaction - seems to work ?

Sassy
Friday, June 25, 2004

I think you might be getting yourself all het up over a *perceived* innefficiency. With pooled connections Koz and Sassy's suggestions work great. "Premature optimisation..." etc etc.

In any case if you insist, then you can batch multiple statements together into one command by simply using a semicolon to separate the statements e.g.
"EXEC MyProc @p0, @p1; EXEC MyProc @p2, @p3; ..."

Try some things out - measure them - I think you'll find it's just not worth worrying all that much about.

Duncan Smart
Friday, June 25, 2004

Pass XML String that contains the data to be inserted to the store proc

Ketul Patel
Saturday, June 26, 2004

>Pass XML String that contains the data to be
>inserted to the store proc

And then parse it myself in T-SQL?  Or is there a better
way? Because if I do the parsing manually, I doubt I'd gain anything performance-wise compared to multiple SP calls.

Mike
Sunday, June 27, 2004

Duncan's got it right... send multiple statements separated by semicolons.

Nick Franceschina
Monday, June 28, 2004

If you want to do this, SQL 2000 has full XML support. 

The following script indicates how to insert multiple rows on a singe round trip:


CREATE PROCEDURE sp_XML_Example
    @XML text
AS

DECLARE
     @idoc int

SET NOCOUNT ON

CREATE TABLE #Temp
    (    ID int,
        Description varchar(50),
    )

    EXEC sp_xml_preparedocument @idoc OUTPUT,  @XML

    INSERT INTO #Temp (ID, Description)

    SELECT ID, Description FROM OPENXML (@idoc, '/XML/ROW',1)  WITH
         (    ID int,
            Description varchar(50)
        )
    
    EXEC sp_xml_removedocument @idoc

    SELECT * FROM #Temp

    DROP TABLE #Temp


Now run this in SQL query analyzer to test it:


DECLARE @XML varchar(4000)

SET @XML = '<XML> <ROW ID="1" Description="Row 1"/><ROW ID="2" Description="Row 2"/></XML>'

exec sp_XML_Example @XML


This shows you *how* to do it.  You may want to take the advice of the other folks as whether you *want* to do it. 

Note that this will work on SQL 2000 only, its not portable.

Canuck
Monday, June 28, 2004

Canuck, thanks a lot, this is almost ideal.

Duncan, I have a question regarding your suggection to use
batch exec's:

>"EXEC MyProc @p0, @p1; EXEC MyProc @p2, @p3; ..."

This seems like an easy way out, however, it doesn't look like
you can enforce the "all or none" rule here. I mean, if the 2nd
EXEC fails, the third will still be executed, and I don't want that. Am I right, or can you work around this?

Mike
Monday, June 28, 2004

BEGIN TRAN; EXEC MyProc @p0, @p1; EXEC MyProc @p2, @p3; ... COMMIT TRAN;

Duncan Smart
Tuesday, June 29, 2004

... Or create a SqlTransaction in your code and associate with the SqlCommand (which results in a similar thing to the above).

Duncan Smart
Tuesday, June 29, 2004

Unfortunately,  it looks like the last EXEC is what determines the success/failure of the batch. So, if EXEC #2
fails but #3 succeeds, the transaction will be committed.  Here's my sample code:

begin tran
exec AddToTestTable 9, 'ab'; exec AddToTestTable 10, 'cd';
if @@error<>0
rollback tran
else
commit tran

The test table has 2 fields, an int primary key and a
varchar. It already has a record with 9, but not 10, so the
first exec will fail and the second will succeed. You would
expect that the transaction will be rolled back, but when I
run this, 1 record gets added to the table because @@error
is set  to 0 in the end.

Mike
Tuesday, June 29, 2004

Seriously,

Have you benchmarked the actual cost of 5 inserts?  These problems are something you should face once you *know* your have a problem.

Koz
Wednesday, June 30, 2004

To bring closure to this thread, I'll just say that the solution
we decided to go with is Duncan's suggestion to use the
batch EXEC's wrapped in a transaction in C#. Somehow -  and it kind of puzzles me - you can enforce a transaction in C# but not in T-SQL (I already wrote about the latter).
I realize that we might not be much better off doing this compared to just calling the DB N times, but as of the moment, this seems like the optimal solution.

Again, thanks to everyone who voiced their opinion, and to Duncan in particular.

Mike
Friday, July 09, 2004

Mike:

your error checking fails, because you are checking after the last exec. It should be something like that in order to work:

begin tran
exec AddToTestTable 9, 'ab';
if @@error = 0
exec AddToTestTable 10, 'cd';
endif
if @@error<>0
rollback tran
else
commit tran


From SQL Server Book Online:
"Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement validated, or save it to a local variable that can be checked later."

Sunny
Sunday, August 01, 2004

*  Recent Topics

*  Fog Creek Home