There are times in a programmers life when you need to find a way to insert a lot of data into a SQL Server database table as fast as possible, and you’re not concerned about constraints being enforced or triggers being fired. If that’s your forte, then you’ve come to the right place.
SQL Server’s Bulk Copy Protocol has been around for a long time. It has a significant performance advantage over other methods, because SQL Server basically just inserts the data into a table, bypassing many SQL Server operations.
The SqlBulkCopy class, in the System.Data.SqlClient namespace, was introduced with ADO.NET 2.0, and was designed to make working with SQL Server’s Bulk Copy feature easy. So having said that, let’s take a look at how it works.
To begin with, create a new SQL Server Database named “Test”. Add a new table to the database named “Items”. Add a “ItemCode” column, of “int” data type; add a “ItemDesc” column, of “varchar(20)” data type.
Once you have the SQL Server Database created, with a “Test” table in place, you’re ready to work with the code below.
And just for fun, let’s throw in a StopWatch so we can see how long it takes to perform the insert!
Some things to note:
The SqlBulkCopy class constructor is overloaded, so it can take just a connection string; a connection string and SqlBulkCopyOptions; just a SqlConnection; or a SqlConnection, SqlBulkCopyOptions, and a SqlTransaction (which enables a roll back if necessary).
The SqlBulkCopy class’ WriteToServer method is also overloaded, so it can take rows from a DataRow() array, a DataTable or a DataReader.
The fasted SqlBulkCopyOptions is “KeepNulls”, because the default column values in the table are ignored. The “Default” option inserts rows, filling in default column values where necessary.
For more information, see SqlBulkCopy on MSDN.
The System.Data.SqlClient.SqlBulkCopy class can be used to quickly and efficiently insert rows from a DataRow() array, DataTable or DataReader directly into a SQL Table, bypassing many SQL Server operations.
Because the SqlBulkCopy class has a significant performance advantage over other methods, it makes it the insert method of choice when you need fast, direct inserting of data into a SQL Table.