All geek questions in one place

Updating Thousands of Records in a DataSet for SQL Server

I have half a million records in a dataset, of which 50,000 are updated. Now I need to transfer the updated records back to the SQL Server 2005 database.

What is the best and most effective way to do this, given the fact that such updates can be frequent (although concurrency is not a problem, but performance)

+4
source share
2 answers

I would use batch update .

Also registered here .

+5
source

I agree with David's answer as to what I use. However, there is an alternative approach that you can take that is worth considering (all situations are different after all) - this is what I will consider in the future if I had another similar requirement.

You can insert updated records into a new table in the database (using SqlBulkCopy ), which is an extremely fast way to load data into db ( example ). Then run the UPDATE statement in your main table to pull the updated values ​​from this new table that you would lose at the end.

The batch approach to using SqlDataAdapter makes it easy to cope with any errors in certain rows (for example, you could say that it will continue to work in case of an error with a certain updated row so that it does not stop the whole process).

+2
source

Source: https://habr.com/ru/post/1309633/

More articles:


All Articles