Part 19 - Copying data from one table to another table using SqlBulkCopy

Suggested Videos
Part 16 - Dataset.rejectchanges and dataset.acceptchanges methods
Part 17 - Strongly typed datasets
Part 18 - Load xml data into sql server table using sqlbulkcopy



In this video we will discuss copying data from one table to another table. The source and destination tables may be in the same database or in different databases and these database can be on the same sql server or in different servers. In Part 18 we discussed, loading xml data into sql server table using sqlbulkcopy. We will be continuing with the example we worked with in Part 18.




Step 1 : Create a new database. Name it SourceDB. Execute the following sql script to create Departments and Employees tables, and to populate with data.

Create table Departments

(

     ID int primary key identity,

     Name nvarchar(50),

     Location nvarchar(50)

)

GO

Create table Employees

(

     ID int primary key identity,

     Name nvarchar(50),

     Gender nvarchar(50),

     DepartmentId int foreign key references Departments(Id)

)

GO

Insert into Departments values ('IT', 'New York')

Insert into Departments values ('HR', 'London')

Insert into Departments values ('Payroll', 'Muumbai')

GO

Insert into Employees values ('Mark', 'Male', 1)

Insert into Employees values ('John', 'Male', 1)

Insert into Employees values ('Mary', 'Female', 2)

Insert into Employees values ('Steve', 'Male', 2)

Insert into Employees values ('Ben', 'Male', 3)

GO

Step 2 : Create another new database. Name it DestinationDB. Execute the just the create sql script to create Departments and Employees tables. Here we have just the structure of the tables and no data. We will be moving data from SourceDB tables to DestinationDB tables.

Step 3 : Include the following 2 connection strings for the Source and Destination databases in the web.config file of the Demo project we created in Part 18.

<connectionStrings>

  <add name="SourceCS"

        connectionString="server=.;database=SourceDB;integrated security=true"/>

  <add name="DestinationCS"

        connectionString="server=.;database=DestinationDB;integrated security=true"/>

</connectionStrings>

Step 4 : Copy and paste the following code in the button click event handler method in the code-behind file

string sourceCS = 

      ConfigurationManager.ConnectionStrings["SourceCS"].ConnectionString;

string destinationCS = 

      ConfigurationManager.ConnectionStrings["DestinationCS"].ConnectionString;

using (SqlConnection sourceCon = new SqlConnection(sourceCS))

{

    SqlCommand cmd = new SqlCommand("Select * from Departments", sourceCon);

    sourceCon.Open();

    using (SqlDataReader rdr = cmd.ExecuteReader())

    {

        using (SqlConnection destinationCon = new SqlConnection(destinationCS))

        {

            using (SqlBulkCopy bc = new SqlBulkCopy(destinationCon))

            {

                bc.DestinationTableName = "Departments";

                destinationCon.Open();

                bc.WriteToServer(rdr);

            }

        }

    }

    cmd = new SqlCommand("Select * from Employees", sourceCon);

    using (SqlDataReader rdr = cmd.ExecuteReader())

    {

        using (SqlConnection destinationCon = new SqlConnection(destinationCS))

        {

            using (SqlBulkCopy bc = new SqlBulkCopy(destinationCon))

            {

                bc.DestinationTableName = "Employees";

                destinationCon.Open();

                bc.WriteToServer(rdr);

            }

        }

    }

}

Note: Please make sure to include the following using declarations

using System;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

As the column names in the source and destination tables are the same column mappings are not required.