How do I call stored procedures in bulk? I would like to do something like a bulk copy.
All that the stored procedure does is 8 selects for unique constraint and 8 inserts. With no returning value.
+2
A:
You cannot do that.
Bulk copy is a firehose dump of data into a table, you cannot call sprocs or anything else instead of just dumping it into an existing table.
What you can do, however, is dump the data using bulk copy into a temporary table with the right structure, and then afterwards call your sproc that moves that data into the real tables, possibly by modifying existing data instead of inserting it, or whatnot.
Lasse V. Karlsen
2010-01-29 22:27:07
Is that a elegant solution? What if I were to change the parameter of the stored procedure to an array and fire like 1000 objects into it at a time, and have the stored procedure walk though the array?
Will
2010-01-29 22:58:08
Why don't you try it and see? If both solutions work, I would call them elegant since neither looks problematic in theory. For me, with my knowledge, it would be easier to do bulk-copy + sproc instead of multiple sprocs with arrays since I haven't done that, but you should try it. You're the one that has to maintain the code, so only you know what will be best for you. I would look at performance characteristics as well and I can't really tell if one would be vastly better than the other.
Lasse V. Karlsen
2010-01-29 23:03:19
A:
If you want to bulk load data into a table (inserts), the SqlBulkCopy class is the way to go.
Alternatively, you can use the SqlDataAdapter. Set the InsertCommand to the stored procedure that will perform an insert, and map the datatable fields to the sproc parameters. If you have updated records in the datatable, you can also specify an UpdateCommand which will be fired for each updated row. Then call the Update method on the SqlDataAdapter passing it the datatable. You can set the UpdateBatchSize property to define how many records to send to the db in each roundtrip.
AdaTheDev
2010-01-29 22:30:52
A:
SqlServer stored procedures can accept xml, so you could prepare your bulk data as an xml file and pass it to a special-purpose stored procedure which would then call your original stored procedure for each row. You'd need the OPENXML function.
I hesitate to recommend the xml features of SqlServer, but this may be a case where they are appropriate.
Gabe Moothart
2010-01-29 23:16:41