web analytics

SqlBulkCopy – Bulk Insertion in SQL Server from .NET

Reading Time: 1 minute

Many a times there come a scenario when you have to insert large number of records in sql server. There are number of ways to implement this scenario:

  • through looping techniques to call insert statements for one record at a time.
  • Serialize the data in CSV or XML format and send them as a parameter to a stored procedure and then through parsing execute insertion statements.
  • generate a file and load them through a SSIS package.
  • Use SqlBulkCopy.

SqlBulkCopy is way faster than multiple insert statements, serializing/ deserializing the data, or saving the data out to a file system and running an import.  Its also has no limit on the data you can send across and very efficient in the way it handles inserts.

This is how simple it is to use it. In the example we have a function that writes copies are DataTable into a MS SQL database table called “tblInsertion”.

using System.Data.SqlClient;

Function WriteToDB(DataTable dt)

{

SqlBulkCopy sqlBC = new SqlBulkCopy(dbconnectionstring);

sqlBC.BatchSize = 25000;

sqlBC.BulkCopyTimeout = 60;

sqlBC.DestinationTableName = “dbo.tblFooBar” ;

sqlBC.WriteToServer(dt);

}

Leave a Reply

%d bloggers like this: