Moving Large Amounts of Data Using SqlBulkCopy

Moving Large Amounts of Data Using SqlBulkCopy

BCP (Bulk Copy) and DTS (Data Transformation Services) both work much faster than  a row by row SQL statement insert would be.

In .NET Framework  if we want  to implement the functionality of copying over vast amounts of data between databases then  the DataSet or data adapter would have been a very poor

option because filling the DataSet, sending it across, changing all row states, iterating over each single row, and executing one SQL command per row would take so long  time .

.NET 2.0,  has introduced a new class specifically for this purpose: the SqlBulkCopy class. , SqlBulkCopy copies data from one table to

 another over two different open  SqlConnections. The two different open SqlConnections can also point to the same database if
that is what you prefer, or they may point to different databases.
There is following step using BCP:-

1. Start a  new Console application, and name it SqlBulkCopy.

2. The aim  of this application is to  fast copying of data between

two tables of identical structure. So  first step is to create two tables of identical

structure. You can easily achieve this with the following SQL statement:

Create Table student as Select * from student  where 1 = 2

3. we will  need two different SqlConnections: one for the data reader that the

SqlBulkCopy will read from, and one for SqlBulkCopy itself. So let’s start by creating the

data reader that will read from the Student  table. This can be seen in the following code:

using (SqlConnection sqlconn = new SqlConnection(connectionString)) { SqlCommand sqlcomm = sqlconn.CreateCommand(); sqlcomm.CommandText = "Select * from student"; sqlconn.Open(); SqlDataReader dr = sqlcomm.ExecuteReader(); } // Dispose is called on firstConnection

4.The second part of the application uses the SqlBulkCopy object instance to insert data

read from the created data reader into the StudentCopy table.

using (SqlConnection sqlconn = new SqlConnection(connectionString)) { SqlCommand sqlcomm = sqlconn.CreateCommand(); sqlcomm.CommandText = "Select * from Student"; sqlconn.Open(); SqlDataReader dr = sqlcomm.ExecuteReader(); using (SqlConnection secondConnection = new SqlConnection(connectionString)) { SqlBulkCopy bc = new SqlBulkCopy(secondConnection); bc.DestinationTableName = "StudentCopy"; bc.WriteToServer(dr); bc.Close(); dr.Close(); } // Dispose is called on sqlconn }

5. That’s it. Compile and run the application to copy rows from one table to another in

a screamingly fast manner. Connect to a larger table and write up an equivalent application leveraging DataSets

 and data adapters, and notice the time it takes in comparison with SqlBulkCopy. You can see that SqlBulkCopy

 is hundreds to thousands of times faster.