Menu
SqlBulkCopy is supposed to help do large volume of inserts instead of sending individual insert statements. But what about calling stored procedures? I have a procedure that the data gets passed to it and then it does some lookups in another table and possibly a second insert into that lookup table.
Since this can't be converted to a query, is there a way to use SqlBulkCopy for stored procedure calls or would it not make any sense?
I'm only making 2000 or less calls at a time per db connection but wanted to know if there was a more efficient way.
988
The SqlBulkCopy class lets you write managed code solutions that provide similar functionality. There are other ways to load data into a SQL Server table (INSERT statements, for example), but SqlBulkCopy offers a significant performance advantage over them.
Upload the data to the temporary table, then perform the SqlBulkCopy update. Using SqlBulkCopy(), upload the datatable's data to the temporary table. Then execute a SQL command to update the main table's data from the temporary table. Finally drop the temporary table.
A batch is complete when BatchSize rows have been processed or there are no more rows to send to the destination data source. Zero (the default) indicates that each WriteToServer operation is a single batch.
By default, a bulk copy operation is its own transaction. When you want to perform a dedicated bulk copy operation, create a new instance of SqlBulkCopy with a connection string, or use an existing SqlConnection object without an active transaction.
Based on @Kev answer, you can do the bulk insert into a staging table and then you can have a trigger kick off your stored procedure.
I'm assuming you have a FK constraint to your other table so you'll need the value before inserting into the target table. You might try removing the constraint if possible. Do your bulk insert then the trigger can just update the columns afterward.
129
The only way can think of doing this would to have a trigger on the destination table and set the SqlBulkCopyOptions FireTriggers option.
From there you could call your stored procedure or put the stored procedure logic in that trigger.
If the table is appended to by other clients (e.g. a web app) then you'd need to have some way of differentiating between the bulk copy client and other regular apps. I guess you could differentiate by setting the Application Name value in the connection string and checking for it in the trigger using SELECT APP_NAME().
38
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
