9

I have a datatable with the records.I'm inserting records into Sql table using SqlBulkCopy.It works fine.Next time when get the datatable with same records with few changed values SqlBulkCopy is inserting another set of records without updating the previous details.How can I update the Sql table using SqlBulkCopy ?? Please help.

Thanks, Vix

Improve this question

4 Answers 4

Reset to default
16

SqlBulkCopy is only used for inserting records, not updating them as explained here. You'd need to use a different technique to do bulk updates.

e.g. you could SqlBulkCopy into a staging table, then run some SQL to update from there to the main table.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

+1 for staging table, that's exactly how you must do updates with bulk-insert, and for massive amounts of updates on the same tables, it's well worth the effort to do it.
3

Truncate the table and perform Bulkcopy.

Improve this answer

2 Comments

for vix: Truncate then bulk load is the most efficient process if you only want the resulting table to contain only the bulk load records. - upvoted
Fine for some simple situations, but this would be a real pain if any level of complexity in the data you are updating. Maintaining your data integrity could be very difficult depending on particulars of app / DB. e.g. FK relationships need to be retained.
3

Avoid Truncate table and Create a new temporary table, which BTW consume more space and memory.

I created a Trigger with INSTEAD OF INSERT and use inside MERGE statement.

But don't forget add the parameter SqlBulkCopyOptions.FireTriggers in the SqlBulkCopy.

This is my two cents.

Improve this answer

Comments

-2

Like mentioned by AdaTheDev, SqlBulkCopy can only insert however there is an alternative library which allow to perform Upsert operations.

Disclaimer: I'm the owner of the project Bulk Operations

The Bulk Operations library has a method "BulkMerge" which Insert or Update rows based on the specified key.

var bulk = new BulkOperation(connection);

bulk.ColumnMappings.Add("ID", true);
bulk.ColumnMappings.Add("Column1");
bulk.ColumnMappings.Add("Column2");
bulk.ColumnMappings.Add("Column3");

bulk.BulkMerge(dt);
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.