How To Copy Table Data Between SQL Servers at High Speeds - Through SqlBulkCopy

SqlBulkCopy is a new feature in ADO.NET 2.0 that gives you DTS-like speeds when you need to programmatically copy data from one database to another. Late last night I needed to harness SqlBulkCopy when for some unknown reason my beloved Red Gate Tools kept hanging when trying to transfer data from SQL Server 2000 to SQL Server 2005. Lucky for me my situation was simple. I had identical tables on both databases. I just needed to populate those empty tables on SQL Server 2005 with the data on SQL Server 2000. It took me about 20 minutes to write the code as SqlBulkCopy does the bulk of the work. Shown below is my slapped-together CopyData Class that accepts 2 connection strings, one for the source database and one for the destination database. A single method CopyTable is called with the name of the table whose data needs to be transferred from one database to another. /// /// CopyData /// public class CopyData { string _sourceConnectionString; string _destinationConnectionString; public CopyData(string sourceConnectionString, string destinationConnectionString) { _sourceConnectionString = sourceConnectionString; _destinationConnectionString = destinationConnectionString; } public void CopyTable(string table) { using (SqlConnection source = new SqlConnection(_sourceConnectionString)) { string sql = string.Format("SELECT * FROM [{0}]", table); SqlCommand command = new SqlCommand(sql, source); source.Open(); IDataReader dr = command.ExecuteReader(); using (SqlBulkCopy copy = new SqlBulkCopy(_destinationConnectionString)) { copy.DestinationTableName = table; copy.WriteToServer(dr); } } } } It assumes you passed in valid connection strings, the databases actually exist, and the table exists at both databases. If not, you can count on an unhandled SqlException being thrown at you. SqlBulkCopy has the option of accepting an IDataReader as input, so the CopyTable method opens up a connection to the source database and does a “SELECT *” on the source table using the SqlCommand object's ExecuteReader. An instance of SqlBulkCopy is created and passed the connectionstring of the destination database in its constructor. The name of the destination table is provided and WriteToServer takes all the information from the IDataReader and puts it in the empty destination table. Copying table data is as simple as: CopyData copier = new CopyData(".ConnectionString1.", ".ConnectionString2."); copier.CopyTable(".TableName.");
Reactions

Post a Comment

1 Comments

  1. this is really very useful.
    Thanks a lot.
    thankful
    Neelesh Jain

    ReplyDelete

Please do not post any spam link in the comment box😊

Emoji
(y)
:)
:(
hihi
:-)
:D
=D
:-d
;(
;-(
@-)
:P
:o
:>)
(o)
:p
(p)
:-s
(m)
8-)
:-t
:-b
b-(
:-#
=p~
x-)
(k)

Close Menu