Monday, December 6, 2010

Bulk insert by SqlBulkCopy

SqlBulkCopy is great option provided by microsoft to insert bulk records with flexibilty to define BatchSize and BulkCopyTimeout. I have used it ,it is really very fast as it does not write logs in database.

following is example for SqlBulkCopy.

private void BulkInsertData(DataSet objDS)
if (objDS != null)
if (objDS.Tables[0] != null)
DataTable sourceData = objDS.Tables[0];

using (SqlConnection destinationConnection = new SqlConnection(connectionString))
// open the connection
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection.ConnectionString))
bulkCopy.BulkCopyTimeout = 0; //sets no time limits
bulkCopy.BatchSize = 5000;
// column mappings
bulkCopy.ColumnMappings.Add("A", "A");
bulkCopy.ColumnMappings.Add("B", "B");
bulkCopy.ColumnMappings.Add("C", "C");
bulkCopy.ColumnMappings.Add("D", "E");

bulkCopy.DestinationTableName = "DestinationTableName";



Map the columns required for bulk insert and provide data in datatable to function WriteToServer which will do work of bulk insert to MSSql table.

No comments: