Using SQLBulkCopy to Insert/Update database

Question

Using SQLBulkCopy to Insert/Update database

asp.net sqlbulkcopy

Question

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

vix

Popular Answer

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.

AdaTheDev

View more on Stack Overflow

Jonathan Magnan · Accepted Answer · 3/11/2016 6:29:53 PM

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);

Using SQLBulkCopy to Insert/Update database

Question

Fastest Entity Framework Extensions

Expert Answer

Popular Answer

Performance

Expression Evaluator