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.

Createtable Departments

(

ID intprimarykeyidentity,

Name nvarchar(50),

Location nvarchar(50)

)

GO

Createtable Employees

(

ID intprimarykeyidentity,

Name nvarchar(50),

Gender nvarchar(50),

DepartmentId intforeignkeyreferences Departments(Id)

)

GO

Insertinto Departments values ('IT','New York')

Insertinto Departments values ('HR','London')

Insertinto Departments values ('Payroll','Muumbai')

GO

Insertinto Employees values ('Mark','Male', 1)

Insertinto Employees values ('John','Male', 1)

Insertinto Employees values ('Mary','Female', 2)

Insertinto Employees values ('Steve','Male', 2)

Insertinto 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.