CODEWITHSUNDEEP
.netsql-serverdatatable
Joe
Joe

Reputation: 379

Best way to update a sql server table from a datatable

We get excel spreadsheets that we need to update a sql server database with. I can easily read the spreadsheet into a datatable, it can have thousands of rows. So I can have a datatable similiar to this:

id    data
1       A
2       B

ect.

I have a database table like this:

id    data    (more columns)
1       Y
2       Z

There can be more than one data field, it is unpredictable. I need to change data for id 1 to A.

I have a text box where I can type SQL UPDATE ITEMS SET data = {0} WHERE id = {1} that will do all the updates.

I can modify the SQL in the textbox as situation require. I can also assign columns in the datatable to the placeholders, {0} and {1} in this example.

I was wondering if I can somehow join to the datatable with SQL OR if it could be worth it to insert the entire datatable into a temp table then do a mass update. Or should I leave it as is and loop through the datatable.

Upvotes: 1

Views: 511

Answers (1)

Pawel
Pawel

Reputation: 380

If I understand You correctly, You can use MERGE (https://msdn.microsoft.com/en-us/library/bb510625.aspx) statement. Create DataTable and fill it up with data from excel spreadsheet. Then pass those data as a parameter of (created earlier) custom table-type variable for sql procedure which uses merge statement to update data. It is very fast!

Upvotes: 1

Related Questions