Bulk Copy In SQL Server Using C#

Introduction

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.

Create Table Products
(
[Id] int primary key,
[Name] nvarchar(50),
[Desc] nvarchar(250)
)
GO
Declare @Id int
Set @Id = 1
While(@Id <= 100000)
Begin
Insert into Products values
(@Id, 'This Is Product No - ' + CAST(@Id as nvarchar(20)),
'It Contains Total Product - ' + CAST((@Id+10) as nvarchar(20)))
Print @Id
Set @Id = @Id + 1
End

Step 2. Empty destination table

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

Create Table Products
(
[Id] int primary key,
[Name] nvarchar(50),
[Desc] nvarchar(250)
)

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:

<connectionStrings>
<add name="Source" connectionString="Data Source=*****; Initial Catalog=manish_db;
User Id=sa; password=*****" providerName="System.Data.SqlClient"/>
<add name="Destination" connectionString="Data Source=*****; Initial Catalog=TestDB;
User Id=sa; password=*****" providerName="System.Data.SqlClient"/>
</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:

<div style ="font-family :'Times New Roman' ">
<asp:Button ID="btnCopy" runat="server" OnClick="btnCopy_Click"
Text="Copy Product Table Data To Test DB Product Table" Width="398px" />
<br />
<asp:Label ID="lblProgRpt" runat="server" ForeColor="Red"></asp:Label>
<br />
<asp:Label ID="lblMsg" runat="server" ForeColor="Green"></asp:Label>
</div>

Step 4. Bulk copy data

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

private void BulkCopy()
{
string Source = ConfigurationManager.ConnectionStrings["Source"].ConnectionString;
string Destination = ConfigurationManager.ConnectionStrings["Destination"]
.ConnectionString;
using (SqlConnection sourceCon = new SqlConnection(Source))
{
SqlCommand cmd = new SqlCommand("SELECT [Id],[Name],[Desc] FROM [manish_db].[dbo]. Products]", sourceCon);
sourceCon.Open();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
using (SqlConnection destinationCon = new SqlConnection(Destination))
{
using (SqlBulkCopy bc = new SqlBulkCopy(destinationCon))
{
bc.BatchSize = 10000;
bc.NotifyAfter = 5000;
bc.SqlRowsCopied +=(sender, eventArgs) =>
{
lblProgRpt.Text += eventArgs.RowsCopied + " loaded...."
+ "<br/>";
lblMsg.Text = "In " + bc.BulkCopyTimeout +
" Sec " + eventArgs.RowsCopied + "
Copied.";
};
bc.DestinationTableName = "Products";
bc.ColumnMappings.Add("Id", "Id");
bc.ColumnMappings.Add("Name", "Name");
bc.ColumnMappings.Add("Desc", "Desc");
destinationCon.Open();
bc.WriteToServer(rdr);
}
}
}
}
}

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:

bc.DestinationTableName = "Products"; // Destination table Name
bc.ColumnMappings.Add("Id", "Id"); // source table columns and destination table
//columns mapping
bc.ColumnMappings.Add("Name", "Name");
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.

Summary

This article showed how to bulk copy data from one table to another table between two different databases.

7 Comments

Join the conversation! Your thoughts help the community grow.

Harsh Patel4y ago
Very Helpfull!
Pankajkumar Patel7y ago
Nice once ...
shOckz Fringe10y ago
Nice! Thank you for sharing. will this work from SQL server to Amazon Redshift?
Kamlesh Tiwari10y ago
Nice Artical, thanks for sharing !!!
- Manish Kumar Choudhary10y ago
  Thanks Kamlesh Tiwari.
shravan kumar11y ago
nice article. thanq for sharing...
- Manish Kumar Choudhary10y ago
  Thanks shravan kumar