Bulk Copy in SQL server using c#.net

Introduction

This article explains how to copy data of one table to another table using bulk copy. In this example I am explaining a scenario when both tables are in two different databases in same server.

First of all design a table by using following scripts in one database and insert some data in it.

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

In above table total 100000 rows are avalibe now create another database and create same table structure of table in that databse. If you want you can create different structure and collumns name also.

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

Now open visual studio and create an empty web project. In web.config file create two connection string like

<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 for reading data from first database product table and Destination connection string for access another database table and paste the data.

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>

Now in code behind page of this webpage create following function:

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);
                        }
                  }
            }
      }

}

In above code

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

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

Here 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 structure 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 copy the data of one table to another table of another database using bulk copy.Thanks.I would like to get feedback from my readers. Please post your feedback, question, or comments about this article.