Bulk Copy Data Using SQLBulkCopy Class

On August 16, 2008 · 4 Comments

SQLBulkCopy class in ADO.NET can be used to do bulk copy operations from a .Net application. There are two ways data can be bulk copied using this class. You can either do a single bulk copy or a multiple bulk copy operation. In this post I will show you how to perform a single bulk copy operation. I will copy all data in [order details] table from one Northwind database to another. Before running the code I have already cleared the [order details] table in destination database using a truncate statement.

Here is a complete sample which copies data from my Northwind database to Northwind2 database:

static void Main(string[] args)

    /******************************************************************

     * Bulk copy Order Details data from Northwind to Northwind2 database

     *****************************************************************/

    string northwindConnectionString =

        "Data Source=.;Initial Catalog=Northwind;Integrated Security=True";

    string northwind2ConnectionString =

        "Data Source=.;Initial Catalog=Northwind2;Integrated Security=True";

    using (SqlConnection northwindConnection =

        new SqlConnection(northwindConnectionString))

        northwindConnection.Open();

        SqlCommand command =

            new SqlCommand("Select * from [order details]", northwindConnection);

        SqlDataReader dataReader = command.ExecuteReader();

        using (SqlConnection northwind2Connection =

            new SqlConnection(northwind2ConnectionString))

            northwind2Connection.Open();

            SqlBulkCopy bulkCopy = new SqlBulkCopy(northwind2Connection);

            bulkCopy.DestinationTableName = "[Order Details]";

            bulkCopy.WriteToServer(dataReader);

            northwind2Connection.Close();

        northwindConnection.Close();

    Console.WriteLine("Bulk Copy Operation Successful");

    Console.WriteLine("Press any key to exit...");

    Console.ReadKey();

While executing the above code, I also ran profiler to see the generated statements. SQLBulkCopy produced the following SQL statement for this bulk copy operation.

insert bulk [Order Details] ([OrderID] Int,

[ProductID] Int,

[UnitPrice] Money,

[Quantity] SmallInt,

[Discount] Real)

SQLBulkCopy is a fast efficient way to do bulk copy operations. It is useful when you are handling large amount of data for simple operations such as copying from one location to another.

Technorati Tags: .NET,ADO.NET

Tagged with: .Net • ADO.NET

4 Responses to Bulk Copy Data Using SQLBulkCopy Class

Arthur HG says:

October 20, 2009 at 3:47 am

Hi,

I have a XLS File and need upload to sql server, I use a sqlbulkcopy…when one throw me a error,How I Know…
What row cannot inserted ?? or What number of row is wrong ??? I need send a message with this information.

Thanks.

Apologize for my bad english
Deepak says:

October 23, 2009 at 1:47 pm

Arthur,
Can you post some code.
Arthur HG says:

October 29, 2009 at 10:58 am

Im not have problem with my code…mmmm

I have a excel file with two columns: Column1,Column2 and also have a table with 2 columns: Column1(int) and Column2(varchar(50))…

Imagine someone want to upload a excel file like this:

|Column1 | Column2 |
|1 |Info |
|2 |Info |
|a |info |
|4 |info |

Obviously this throw a error…. I need show a message like this:
The row 3 in your file is wrong, please correct it.

Thanks for everything!!!

Apologize for my bad english
Bijayani says:

January 28, 2010 at 3:44 pm

Hi,

I happened to see your post find it quite informative. I would like to share a link where a software engineer has shared a tip on “SqlBulkCopy In .NET”.

Here is the link:
http://www.mindfiresolutions.com/SqlBulkCopy-In-NET-215.php

Hope you find it useful and of assistance.

Thanks,
Bijayani