Insert/Update whole DataTable into database table C#

c# sqlbulkcopy sql-server

Question

I am facing an issue I hope to get it solved by here. I have 3 different tables in a DataSet and I want to insert it in the database table.

I know I can do this using SqlBulkCopy but there is a catch and that is I want to check if the data already exists in the database then I want it to get updated instead of insert.

And if the data doesn't exist in the database table, I want to insert it then. Any help on this would be appreciated.

I know I can iterate it through each record and then fire a procedure which will check for its existence if it exists den update or else insert. But the data size is huge and iterating through each record would be a time taking process, I don't want to use this approach.

Regards

Expert Answer

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

This project allows to BulkInsert, BulkUpdate, BulkDelete, and BulkMerge (Upsert).

Under the hood, it does almost what @marc_s have suggested (Use SqlBulkCopy into a temporary table and perform a merge statement to insert or update depending on the primary key).

var bulk = new BulkOperation(connection);
bulk.BulkMerge(dt);


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why