ADO.NET tutorial for beginners - Part 19 Copying data from one table to another table using SqlBulkCopy

Part 19 Copying data from one table to another 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 sql script to just create Departments and Employees tables. Here we have just the structre 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”
providerName=”System.Data.SqlClient” /]
[add name=”DestinationCS” connectionString=”server=.;database=DestinationDB;Integrated Security=True”
providerName=”System.Data.SqlClient” /]
[/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.

Text version of the video
http://csharp-video-tutorials.blogspot.com/2014/09/part-19-copying-data-from-one-table-to.html

Slides
http://csharp-video-tutorials.blogspot.com/2014/09/part-19-copying-data-from-one-table-to_24.html

All ADO .NET Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-adonet-video-tutorial.html

All ADO .NET Slides
http://csharp-video-tutorials.blogspot.com/p/adonet-slides.html

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists