Doing SQL Server batch and bulk updates in C# quickly with SQLBulkCopy.

Loading multiple rows of data into a SQL server table can be fairly timeconsuming, if for instance you have a data warehouse where all records for a customer have to be reloaded and recalculated at intervals.

At Egghead Café I stumbled upon this article about the SQLBulkCopy object, but unfortunately the code only tests the “after” situation, not how it compares with traditional multiple INSERT statements. Also the timers in the code measure somewhat more than the basic upload, which in my opinion should be changed.

Well, I decided to test it out and the results were striking: trying to execute one big batch of INSERT’s simply made the connection time out, and executing the INSERT statements one at a time in my setup with a non-local SQL server 2005 took about 1½ minutes. Compare that to the SQLBulkCopy  which took about 2 seconds!

Conclusion: Look for the SQLBulkCopy object whenever this scenario occurs 🙂

Advertisements
Posted in SQL

One thought on “Doing SQL Server batch and bulk updates in C# quickly with SQLBulkCopy.

  1. Jonathan Magnan

    The ZZZ Projects Bulk Operations Library is the solution for you.

    It allows you to effectuate various operations such as: Delete, Insert, Update and Merge on a substantial amount of data.

    var operation = new SqlBulkOperation();

    // … Custom Settings ….
    operation.BulkDelete(dt);
    operation.BulkInsert(dt);
    operation.BulkUpdate(dt);
    operation.BulkMerge(dt);

    It also offers more advanced features like output value, column formula, auditing, intercepting, logging and more.

    Find out more on http://zzzprojects.com/bulk-operations/ about the most advanced and flexible Bulk Operations library.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s