Import data to Sql Server using SqlBulkCopy (performance boost!)
Recently I came across a situation that I had to upload an excel spreadsheet to a SQL Server DB (both with same schemas). No problem, just use SQL INSERT statements and I'm done right?! Not so fast, the INSERT statement is cool but in case of thousands and even millions of data some performance issues rise up! So, the first approach sucks... And then came the SqlBulkCopy, a major improvement in data import to Sql Server DB's.
It uses the same functionality the Bulk Copy Program utility provides. It allows you to copy large amounts of data from a variety or sources to your Sql Server Table without compromising much of the performance. One article on eggheadcafe by Peter Broomberg shows how long does it take to copy 43.000 records from a csv file to Sql Server (if I'm not mistaken, about 1250 milliseconds).
So I decided to try it myself and boom... Extreme successful result!!!
Here is a code snippet of my code:
DataTable dt = ReadFromExcel();
string connectionString = @"Server=QUIZER\SQLEXPRESS;Initial Catalog=SqlBulkCopyTest;Uid=submission;Pwd=submission";
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlBulkCopy copy = new SqlBulkCopy(conn))
{
conn.Open();
copy.DestinationTableName = "TestTable";
try
{
copy.WriteToServer(dt);
}
catch (Exception ex)
{
logTime.WriteLine("Error occured : " + ex.Message);
}
}
}
To import 65535 excel rows it took about 4 seconds (including exporting excel data to a DataTable). I've tried importing using normal insert statements and it tool about 7 minutes and 30 seconds.
No match :).
Cheers