.NET Tips & Tricks Community RSS

Those who prefer reading this site through RSS now can also subscribe for updates to .NET Tips & Tricks Community.

Speed up inserting records into database with SqlBulkCopy class

When you need to insert a great deal of rows into database (for example, when importing data from a flat file or when importing data from one database into another one), and you need to do it programmatically because it needs to be pre-processed on the fly, then you should use SqlBulkCopy class.

The class uses Tabular Data Stream for fast transmitting data from client to a database server (therefore you'll see a bunch of "strange" INSERT BULK statements in a SQL Profiler):

    DataTable dataTable = new DataTable();
    ...
 
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = "dbo.DestinationTale";
        bulkCopy.WriteToServer(dataTable);
    }