Is there any BulkUpdate Command similar to BulkCopy in SQL Server 2008

c# sqlbulkcopy sql-server sql-update

Question

I have used BulkCopy command to transfer rows from one table to another table with bulk data about 3 to 5 million rows. I want to update these rows.

Is there any BulkUpdate command similar to the BulkCopy command? I'm using ASP.NET with C#.

Expert Answer

A common approach here is:

  • bulk-load (SqlBulkCopy) into an empty *staging table - meaning: a table with the right columns/types as the actual data, but not part of the main transactional system
  • now do an update joining the real data to the staging data, to update the values in the real data

Expert Answer

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

The Bulk Operations Library allow to Insert, Delete, Update and Merge millions of rows in few seconds.

It's very easy to learn and use if you already know the SqlBulkCopy class.

var bulk = new BulkOperation(connection);
// ... Mappings ....

bulk.BulkUpdate(dt);

Popular Answer

No, there isn't.

Q: What's an "lac"?

This might help:

http://itknowledgeexchange.techtarget.com/itanswers/bulk-update-in-sql-server-2005/

Assuming that you have a column with distict values to show you which rows are which between the two tables this can be done with a simple update statement.

UPDATE TableA
SET TableA.A1 = TableB.B1,
    TableA.A2 = TableB.B2  
FROM TableB  
WHERE TableA.A3 = TableB.B3

If you are worried about creating one massive transaction you can batch the operation into smaller chunks. This is done via the TOP keyword.

UPDATE TOP (1000) TableA
SET TableA.A1 = TableB.B1,
    TableA.A2 = TableB.B2  
FROM TableB  
WHERE TableA.A3 = TableB.B3
      AND TableA.A1 <> TableB.B1
      AND TableA.A2 <> TableB.B2

You can put that into a loop...

Here's another link (with basically the same solution):

http://www.sqlusa.com/bestpractices2005/hugeupdate/



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