Friday, December 26, 2008

How to Bulk Insert records to T-SQL Table from a DataTable in C#?

This article will tell you two method of inserting bulk records into a T-SQL table from a DataTable in C#:

1. Bulk insert using the SqlBulkCopy class

2. Bulk insert by passing XML data table to the stored procedure

The first method is used ONLY to insert the data to the database and is simple as compared to the second method. The second method can be used to do some complex processing while inserting/updating the data in the SQL table.

In the first method we will make use of the SqlBulkCopy class provided in .NET. Following code illustrates the use of the SqlBulkCopy class for bulk insert:

#region references
using System.Data;
using System.Data.SqlClient;
#endregion references

namespace SQLBulkInsert
{
class Program
{
static void Main(string[] args)
{
// T-SQL Connection
string connection = "Data source=.; Initial Catalog= MyDatabase; SSPI=true";
DataTable dtData = new DataTable();

// Get the data into the DataTable
//dtData = GetData(...);

// Create an object of SqlBulkCopy
SqlBulkCopy objSBC = new SqlBulkCopy(connection);
// Specify the destination table
objSBC.DestinationTableName = "Table_Name";
// Write the data to the SQL Server
objSBC.WriteToServer(dtData);
}
}
}


You can find the implementation of the second method in my article on CodeProject.com

Hope this helps you!

3 comments:

Anonymous said...

good article :)

Manoj Goswami said...

But in this process Triggers are not get fired..
Can u sugges me another options

Anonymous said...

thank you thank you thank you thank you thank you thank you thank you thank you thank you thank you . this worked beautifully for me. imported close to 100 million rows VERY quickly

Post a Comment