• Using SQL Bulk Copy to load data from Source DB to Destination DB

    Posted on March 27, 2012 by in ADO.NET, C#, Dotnet

    How often you try to copy data to a temp table for your testing purpose? How often you try to run your test cases on production copies? Good luck with inserting or copying row by row. It not only eats up all the memory but degrades the performance drastically. In these scenarios, using SQL Bulk Copy is your best bet. Without wasting anytime, lets’ get to the usecase.

    UseCase:
    We would like to do some analysis on employee data. We would like to import that data from PROD DB to your local DB for further investigation.

    Solution:
    Step 1: Use below connection strings for source and destination database connections. 

            /// <summary>
            /// Source database connection string
            /// </summary>        
            public static string GetSourceConnectionString()
            {
                return @"Password=testuser;Persist Security Info=True;
                User ID=testuser;Initial Catalog=TEST;Data Source=";
            }
    
            /// <summary>
            /// destination database connection string
            /// </summary>  
            public static string GetDestinationConnectionString()
            {
                return @"Password=testuser;Persist Security Info=True;
                User ID=testuser;Initial Catalog=TEST;Data Source=";
            }

    Step 2: For opening & closing connections we would do it the traditional way of using opening connection using SqlConnection.Open and using “using” contruct to take care of the dispose part.

    using (SqlConnection sourceConnection =
                           new SqlConnection(GetSourceConnectionString()))
                {
                    sourceConnection.Open();                
                    ..............
                     // Open the destination connection. 
                    using (SqlConnection destinationConnection =
                               new SqlConnection(GetSourceConnectionString()))
                    {
                        destinationConnection.Open();
                        .........                   
                    }
                }

    Step 3: Initialize the command object with a select query to fetch the information we need from tblEmployee table

    // Get the data from the source table as a SqlDataReader.
    SqlCommand commandSourceData = new SqlCommand(@"SELECT [EmployeeID], [EmployeeName,[EmployeeSSN], [EmployeeState]FROM tblEmployee;", sourceConnection);
    SqlDataReader reader = commandSourceData.ExecuteReader();

    Step 4: Now we have our source data. All that is left is to initialize SQLBulkCopy class with destination connection and write the data from the reader to the destination table.

    using (SqlBulkCopy bulkCopy =
                                   new SqlBulkCopy(destinationConnection))
    {
          bulkCopy.DestinationTableName = "dbo.tblEmployee_Copy";
          bulkCopy.WriteToServer(reader);
     reader.Close(); 
    }

    Complete Example:

    SQL:

    For our testing, created some tables & loaded them up with some data.
    &minus;&minus;Source Table
    CREATE TABLE [dbo].[tblEmployee](
     [EmployeeID] [bigint] NOT NULL,
     [EmployeeName] [varchar](255) NOT NULL,
     [EmployeeSSN] [varchar](10) NOT NULL,
     [EmployeeState] [varchar](50) NOT NULL
    ) ON [PRIMARY]
    
    GO
    
    &minus;&minus;Destination Table
    CREATE TABLE [dbo].[tblEmployee](
     [EmployeeID] [bigint] NOT NULL,
     [EmployeeName] [varchar](255) NOT NULL,
     [EmployeeSSN] [varchar](10) NOT NULL,
     [EmployeeState] [varchar](50) NOT NULL
    ) ON [PRIMARY]
    
    GO
    
    &minus;&minus;Load some sample data into tblEmployee Table
    BEGIN
    
     DECLARE @EmployeeID int, @EmployeeName varchar(255), @ID INT
     DECLARE @EmployeeSSN varchar(10), @EmployeeState varchar(255)
    
     SET @EmployeeName = 'TEST NAME '
     SET @EmployeeSSN = 'SSN '
     SET @EmployeeState = 'TEST STATE '
     SET @ID = 1
     
     WHILE (@ID <= 200)
      BEGIN
     
     INSERT INTO [dbo].[tblEmployee]
        ([EmployeeID]
        ,[EmployeeName]
        ,[EmployeeSSN]
        ,[EmployeeState])
      VALUES
        (@ID
        ,(@EmployeeName + convert(varchar, @ID))
        ,(@EmployeeSSN + convert(varchar, @ID))
        ,(@EmployeeState + convert(varchar, @ID)))
    
     SET @ID = @ID + 1
      END
      
    END
    GO

    Source Code:

    public class Program
        {
            public static void Main(string[] args)
            {
                 // Open a sourceConnection
                using (SqlConnection sourceConnection =
                           new SqlConnection(GetSourceConnectionString()))
                {
                    sourceConnection.Open();
    
                    // Get the data from the source table as a SqlDataReader.
                    SqlCommand commandSourceData = new SqlCommand(
                        @"SELECT [EmployeeID],[EmployeeName]                            
                        ,[EmployeeSSN],[EmployeeState]                    
                        FROM tblEmployee;", sourceConnection);
                    SqlDataReader reader =
                        commandSourceData.ExecuteReader();
    
                     // Open the destination connection. 
                    using (SqlConnection destinationConnection =
                               new SqlConnection(GetSourceConnectionString()))
                    {
                        destinationConnection.Open();
    
                        // Set up the bulk copy object. 
                        // Note that the column positions in the source
                        // data reader match the column positions in 
                        // the destination table so there is no need to
                        // map columns.
                        using (SqlBulkCopy bulkCopy =
                                   new SqlBulkCopy(destinationConnection))
                        {
                            bulkCopy.DestinationTableName =
                                "dbo.tblEmployee_Copy";
    
                            try
                            {
                                // Write from the source to the destination.
                                bulkCopy.WriteToServer(reader);
                            }
                            finally
                            {
                                // Close the SqlDataReader. The SqlBulkCopy
                                // object is automatically closed at the end
                                // of the using block.
                                reader.Close();
                            }
                        }
                    }
                }
            }
    
            /// <summary>
            /// Source database connection string
            /// </summary>        
            public static string GetSourceConnectionString()
            {
                return @"Password=testuser;Persist Security Info=True;
                User ID=testuser;Initial Catalog=TEST;Data Source=";
            }
    
            /// <summary>
            /// destination database connection string
            /// </summary>  
            public static string GetDestinationConnectionString()
            {
                return @"Password=testuser;Persist Security Info=True;
                User ID=testuser;Initial Catalog=TEST;Data Source=";
            }
        }
    
    Be Sociable, Share!
      Post Tagged with , ,

    Written by

    Software architect with over 10 years of proven experience in designing & developing n-tier and web based software applications, for Finance, Telecommunication, Manufacturing, Internet and other Commercial industries. He believes that success depends on one's ability to integrate multiple technologies to solve a simple as well as complicated problem.

    View all articles by

    Email : [email protected]

    Leave a Reply