Speeding up your INSERT's with SqlBulkCopy

With version 2.0 of .NET, we got access to a new class in System.Data.SqlClient called SqlBulkCopy. This class will greatly improve your performance when doing mass inserts into your database.

Using SqlBulkCopy instead of regular business objects doing inserts in a loop, reduced the time to insert about 120K rows from 18 minutes to 3 minutes.

Most examples you find googling SqlBulkCopy will show you an example of how to use the class copying from one table to another. I needed an example of how to assemble my own source data into a DataTable, and then copy that to a new Sql table using SqlBulkCopy. So, here goes.

First, we need an instance of the SqlBulkCopy class. In this example, we're passing along a connection string, and the option to keep the identity of the Id column, we'll be setting up in our source data table:

SqlBulkCopy sbc = new SqlBulkCopy();

We also need the DataTable to use as our source for the copy:

DataTable newTable = new DataTable("MySourceTable");

Once you're done adding rows to your newTable, you can submit the the entire table contents to the server with this statement:

sbc.WriteToServer(newTable);

That's it! In some cases you need to use the SqlBulkCopyMapping class to map columns from your data source, to columns in your target table. There's lots of good documentation on that on a google search.