Bulk Copy In SQL Server Using C#

This article explains how to copy data of one table to another table using bulk copy. In this example I explain a scenario when both tables are in two different databases on the same server. The table where the data is being copied from is the source table and the table in which the data is being copied into is called the destination table. The copy data is being done from a Web application and the code is written in C# and ADO.NET. 

 

Step 1. Source table with records

 

First of all, create a new database table by executing the following scripts in one database and insert some data into it. The following script creates a new table, Products with 100,000 rows.

1. Create Table Products  
2. (  
3. [Id] int primary key,  
4. [Name] nvarchar(50),  
5. [Desc] nvarchar(250)  
6. )  
7. GO  
8.   
9. Declare @Id int  
10. Set @Id = 1  
11.   
12. While(@Id <= 100000)  
13. Begin  
14. Insert into Products values  
15. (@Id, 'This Is Product No - ' + CAST(@Id as nvarchar(20)),  
16. 'It Contains Total Product - ' + CAST((@Id+10) as nvarchar(20)))  
17.   
18. Print @Id  
19. Set @Id = @Id + 1  
20. End  

Step 2. Empty destination table

 

Now we create another database and create the same table structure of table in that databse. 

1. Create Table Products  
2. (  
3. [Id] int primary key,  
4. [Name] nvarchar(50),  
5. [Desc] nvarchar(250)  
6. )  

Note: If you want you can create different structure and different columns, you can do that as well. Just name sure the data type of the columns are same for both source and destination tables.

 

Step 3. Create a Web application 

 

Now open Visual Studio and create an empty Web project. In web.config file, create two connection string like the following:

1. <connectionStrings>  
2. <add name="Source" connectionString="Data Source=*****; Initial Catalog=manish_db; 
3. User Id=sa; password=*****" providerName="System.Data.SqlClient"/>  
4.   
5. <add name="Destination" connectionString="Data Source=*****; Initial Catalog=TestDB;
6.  User Id=sa; password=*****" providerName="System.Data.SqlClient"/>  
7. </connectionStrings>  

Here Source connection string is for the first database and the Destination connection string is for the second database. We will read data from the Products table of the source database and copy into the Products table of the destination database.

Now design a web page by using following code:

1. <div style ="font-family :'Times New Roman' ">  
2.   
3. <asp:Button ID="btnCopy" runat="server" OnClick="btnCopy_Click" 
4. Text="Copy Product Table Data To Test DB Product Table" Width="398px" />  
5. <br />  
6. <asp:Label ID="lblProgRpt" runat="server" ForeColor="Red"></asp:Label>  
7. <br />  
8. <asp:Label ID="lblMsg" runat="server" ForeColor="Green"></asp:Label>  
9.   
10. </div>  

Step 4. Bulk copy data

 

Now in code behind of the page, create the following function that is responsible for bulk copy data:

1. private void BulkCopy()  
2. {  
3.    string Source = ConfigurationManager.ConnectionStrings["Source"].ConnectionString;  
4.    string Destination = ConfigurationManager.ConnectionStrings["Destination"]
5.                         .ConnectionString;  
6.    using (SqlConnection sourceCon = new SqlConnection(Source))  
7.       {  
8.             SqlCommand cmd = new SqlCommand("SELECT [Id],[Name],[Desc] FROM [manish_db].[dbo].                                             Products]", sourceCon);  
9.             sourceCon.Open();  
10.             using (SqlDataReader rdr = cmd.ExecuteReader())  
11.             {  
12.                   using (SqlConnection destinationCon = new SqlConnection(Destination))  
13.                   {  
14.                         using (SqlBulkCopy bc = new SqlBulkCopy(destinationCon))  
15.                         {  
16.                               bc.BatchSize = 10000;  
17.                               bc.NotifyAfter = 5000;  
18.                               bc.SqlRowsCopied +=(sender, eventArgs) =>  
19.                               {  
20.                                     lblProgRpt.Text += eventArgs.RowsCopied + " loaded...." 
21.                                     + "<br/>";  
22.                                     lblMsg.Text = "In " + bc.BulkCopyTimeout +
23.                                      " Sec " + eventArgs.RowsCopied + "
24.                                     Copied.";  
25.                               };  
26.   
27.                               bc.DestinationTableName = "Products";  
28.                               bc.ColumnMappings.Add("Id", "Id");  
29.                               bc.ColumnMappings.Add("Name", "Name");  
30.                               bc.ColumnMappings.Add("Desc", "Desc");  
31.                               destinationCon.Open();  
32.                               bc.WriteToServer(rdr);   
33.                         }  
34.                   }  
35.             }  
36.       }  
37.   
38. }   

As you can see from the above code, the SqlBulkCopy class of ADO.NET is used to bulk copy data.

 

In above code

bc.BatchSize = 10000; 
bc.NotifyAfter = 5000;

BatchSize will read a batch of group of data of 10000 and write them into the destination table. When 5000 data has been written into the destination table, NotifyAfter will rage bc.SqlRowsCopied events and show above message .

Here in the following code:

1. bc.DestinationTableName = "Products"; // Destination table Name  
2. bc.ColumnMappings.Add("Id", "Id"); // source table columns and destination table 
3.                                     //columns mapping  
4. bc.ColumnMappings.Add("Name", "Name");  
5. bc.ColumnMappings.Add("Desc", "Desc");  

If both the source and destination table structures are same then no need of mapping the table columns.

When we run this webpage and click on button it will show output like.