Bulk Insert into SQL Server using SqlBulkCopy

Transferring data from one source to another is common practice in software development. This operation is preformed in many different scenarios which includes migration of the old system to the new system, backing up the data and collecting data from different publishers. ASP.NET 2.0 includes the SqlBulkCopy class that helps to copy the data from different data sources to the SQL SERVER database.

Reliable transaction are maintained by using SqlTransaction object to BulkCopy. In-case of error whole transaction will be role-backed.

Example :

SqlTransaction myTransaction;

DataTable FinalTable = (DataTable)Session[“Data”];

using (SqlConnection cn = Get_Connection())


myTransaction = cn.BeginTransaction();



using (SqlBulkCopy copy = new SqlBulkCopy(cn, SqlBulkCopyOptions.Default, myTransaction))


copy.ColumnMappings.Add(0, 1); //(SOURCE TABLE,DESTINATION TABLE)

copy.ColumnMappings.Add(2, 2); // (“Product Name”,”Name”)

copy.ColumnMappings.Add(3, 3);

copy.ColumnMappings.Add(4, 4);

copy.ColumnMappings.Add(5, 5);

copy.ColumnMappings.Add(6, 6);

copy.ColumnMappings.Add(7, 7);

copy.ColumnMappings.Add(8, 8);

copy.ColumnMappings.Add(9, 9);

//copy.BatchSize = 1000;

copy.DestinationTableName = “dbo.Products”;

copy.WriteToServer(FinalTable); // writing Source Data to sqlserver table




catch (Exception ex)










public bool BulkInsert(DataTable dataTable)

if (dataTable != null && dataTable.Rows.Count > 0)
using (SqlConnection sqlConnection = new SqlConnection(Database.Connection_String))

SqlBulkCopy objSqlBulkCopy = new SqlBulkCopy(sqlConnection) { DestinationTableName = “DestinationTableName” };

foreach (DataColumn dataColumn in dataTable.Columns)
objSqlBulkCopy.ColumnMappings.Add(dataColumn.ColumnName, dataColumn.ColumnName);

return true;
return false;


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s