Update SQL server table using Datatbale

c# datatable sqlbulkcopy

Question

I have in memory table(DataTable), I want to update SQL table with the help of the DataTable in one go(Bulk update). I know how to bulk insert data from DataTable to SQL table. Is there a way to bulk update ?

Accepted Answer

EDITED: Misread the original question. Here's the "bulk update":

UPDATE <table> SET tbl.value = dt.value
    FROM @Datatable dt
    INNER JOIN <table> tbl ON tbl.<id_column> = dt.<id_column>

Popular Answer

You can use one of the MS Enterprise Library Data block's UpdateDataSet() methods.

These will read the rowstate (add, update, delete) of each row and execute the command named in the UpdateDataSet() method. With a reasonable database server, this can handle a few thousand records in a few seconds.

The only drawback I found with this is that it cannot return newly inserted IDENTITY values, but its easy enough to select them from the database after the insert has completed and remap them to the datatable.



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