Home » Articles


Sponsored links

CodeDigest Navigation

Technology News
No News Feeds available at this time.

Community News
No News Feeds available at this time.
Doing Bulk Upload/Insert of DataTable to a Table in SQL server in C#

By Bala Murugan
Posted On Feb 25, 2011
Article Rating:
Be first to rate
this article.
No of Comments: 2
Category: .NetFramework/ADO.Net
Print this article.

Doing Bulk Upload/Insert of DataTable to a Table in SQL server in C#


There will be requirements where we need to import the content of a DataTable object to a table in SQL server. Normally, we will do this by forming separate insert query for each data row in DataTable and firing it over the SQL server using ADO.Net. This approach will be an over head and time consuming when the data in the source DataTable is huge. It will be fast and better, if we can do a bulk upload of the whole DataTable object to the SQL Server table in single shot.  Understanding this, Microsoft shipped a special class called SqlBulkCopy in .NetFramework 2.0 which will help us in doing the same.

Using SqlBulkCopy class

This class can be used for performing bulk copy of data from one table to another table in same database server or different server similar to DTS and bcp utility. It can also be used to bulk copy from Sql server 2005 database table to Sql server 2000 database table, keeping in mind that destination table will have all the columns matching with the one we are copying from the source.

The data source can be anything, SQL Server or DataTable or even DataReader object but with one condition, the destination should be a SQL server.

This SqlBulkCopy class is packed with a method called WriteToServer() which will help us doing the same. Below are the different overloads of the method.


SqlBulkCopy.WriteToServer (DataRow[])

SqlBulkCopy.WriteToServer (DataTable)

SqlBulkCopy.WriteToServer (IDataReader)

SqlBulkCopy.WriteToServer (DataTable, DataRowState)


Moving forward, we will see how to upload the data rows in DataTable object at a shot using SqlBulkCopy class.

For simple understanding, let’s assume you have a DataTable object which has loaded with Employee details. Let’s read the content of the DataTable object and do a bulk upload of the data rows using SqlBulkCopy class.

At a high level, we can do this by following the below steps,

1.      Populate or get the DataTable that needs to be uploaded.

2.      Create a SqlBulkCopy instance by providing an opened connection to the destination server.

3.      Set the ColumnMappings collection. The ColumnMappings collection of SqlBulkCopy needs to be populated with the source and destination column name mapping.

4.      Optionally, set the batch size.

5.      Set the Destination table name in DestinationTableName property of SqlBulkCopy.

6.      Call WriteToServer() method. That’s it! You are done.

The below code does that,

//Get Data from your DataSource or other source

 DataTable dt = GetAPILogData();


 using (_dconn = new SqlConnection(ConfigurationSettings.AppSettings["connection"]))



                    //Do a bulk copy for gaining better performance

                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(_dconn))


                        bulkCopy.ColumnMappings.Add("Name", "EmployeeName");

                        bulkCopy.ColumnMappings.Add("Age", "EmployeeAge");

                        bulkCopy.ColumnMappings.Add("Address1", "StreetAddress1");

                        bulkCopy.ColumnMappings.Add("Address2", "StreetAddress2");

                        bulkCopy.ColumnMappings.Add("City", "City");




                        bulkCopy.ColumnMappings.Add("CreatedDate", "CreatedDate");

                        bulkCopy.ColumnMappings.Add("CreatedBy", "CreatedBy");


                        bulkCopy.BatchSize = 10000;

                        bulkCopy.BulkCopyTimeout = int.Parse(ConfigurationSettings.AppSettings["timeout"]);

                        bulkCopy.DestinationTableName = "EmployeeDetail";






Using SqlBulkCopy Class to do Bulk Insert to SQLServer in C#



There is always a necessity to import data at a shot or in other words, bulk insert in any many projects. The SqlBulkCopy class provides an easy, fast and efficient way to do this task which we understood in this article. Thanks to .Netframework 2.0 which introduced this excellent class with it.

Happy Coding!!


Similar Articles
You can contribute to CodeDiget.Com:
Donate to
Article Feedback