Click here to Skip to main content
Click here to Skip to main content

Application to migrate data between different environments using the ADO.NET 2.0 SqlBulkCopy

, 30 Nov 2006
Rate this:
Please Sign up or sign in to vote.
This article explains how to migrate data between different SQL Server environments.

Introduction

Often need arises to migrate data between different environments for testing or audit purposes. For example, production data can be moved to test environment and this data can be used by testers. This gives a clear idea of how programs behave on actual data volumes. Using this utility, data can be migrated with ease of use. I used SqlBulkCopy of ADO.NET 2.0 for bulk copying data. This managed class provides similar functionality as the BCP utiltity. This program has been used successfully on large volumes of data. It took me approximately 1 hour for loading 8GB of data. More precisely, for loading 1.1 million records, it took 4 minutes and 30 seconds. It supports both SQL 2005 and SQL 2000 Servers. The available features are:

  • Specific tables can be selected for migrating data
  • Deletes existing data in selected tables
  • Reports progress as it loads data
  • Logs results of load or delete operations for every table
  • Can be cancelled in the middle of a process; the application stops loading data after finishing the current table.

Data Migration Screen Shot

Solution walkthrough

This solution contains a C# Windows Forms project. This is a very simple project. A Windows Form does all the work. It uses the BackgroundWorker component to report progress. The source and destination connections are defined in the application configuration file. If any exception happens while deleting or loading data in a table, the application logs the exception and continues to process the next table.

The two main functions in this application are DeleteData and LoadData.

DeleteData

The following tasks are involved in this function:

  • Disable constraints on all tables.
  • Disable triggers on all tables.
  • Delete data from a table (DELETE or TRUNCATE statement used depending on the foreign key constraints on a table). 
  • Log results.
  • Enable constraints on all tables.
  • Enable triggers on all tables.
private void DeleteData()
{
    using (SqlConnection conn = new SqlConnection(strDestConn))
    {
        conn.Open();
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandTimeout = 0;
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            //Disable Constraints on all tables before deleting data
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                cmd.CommandText = "ALTER TABLE [" + 
                    dr["TABLE_SCHEMA"].ToString() + "].[" + 
                    dr["TABLE_NAME"].ToString() + 
                    "] NOCHECK CONSTRAINT ALL";
                cmd.CommandText += " ALTER TABLE [" + 
                    dr["TABLE_SCHEMA"].ToString() + "].[" + 
                    dr["TABLE_NAME"].ToString() + 
                    "] DISABLE TRIGGER ALL";
                cmd.ExecuteNonQuery();
            }

            //Delete data in selected tables and log results
            string strLogFile = "DeleteLog" + 
                   DateTime.Now.ToString("MMddyyyyHHmm") + ".txt";
            using (StreamWriter sw = new StreamWriter(strLogFile, false))
            {
                sw.AutoFlush = true;
                foreach (object dr in lstInclude.Items)
                {
                    try
                    {
                        cmd.CommandText = 
                         "SELECT OBJECTPROPERTY ( object_id('" + 
                         dr.ToString() + "'),'TableHasForeignRef')";
                        int intref = Convert.ToInt32(cmd.ExecuteScalar());
                        if (intref == 1)
                            cmd.CommandText = "DELETE FROM " + dr.ToString();
                        else
                            cmd.CommandText = "TRUNCATE TABLE " + 
                                              dr.ToString();
                        cmd.ExecuteNonQuery();

                        sw.WriteLine("Data deleted successfully from " + 
                                     dr.ToString() + " at " + 
                                     DateTime.Now.ToString());
                        sw.WriteLine();
                    }
                    catch (Exception ex)
                    {
                        sw.WriteLine("Error while deleting data" + 
                                     " in table " + dr.ToString() + 
                                     ".Error is " + ex.Message);
                        sw.WriteLine();
                    }
                }
            }

            //Enable Constraints on all tables
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                cmd.CommandText = "ALTER TABLE [" + 
                    dr["TABLE_SCHEMA"].ToString() + "].[" + 
                    dr["TABLE_NAME"].ToString() + 
                    "] CHECK CONSTRAINT ALL";
                cmd.CommandText += " ALTER TABLE [" + 
                    dr["TABLE_SCHEMA"].ToString() + "].[" + 
                    dr["TABLE_NAME"].ToString() + 
                    "] ENABLE TRIGGER ALL";
                cmd.ExecuteNonQuery();
            }
        }                
    }
}

LoadData

The following tasks are involved in this function:

  • Load data from the source table using a SQL data reader. 
  • Create SqlBulkCopy with KeepIdentity and TableLock options. A lock on the table makes the loading process quicker. By default, SqlBulkCopy disables constraints and triggers on a table while loading data.
  • Map source and table columns. This is necessary if the column ordinal positions are different between the two environments.
  • Eliminate computed columns while mapping columns.
  • Bulk insert data in a table by passing the datareader to SqlBulkCopy.
private void LoadData(string pSourceConn,string pDestConn,string pTable)
{
    using (SqlConnection srcConn = new SqlConnection(pSourceConn))
    {
        string[] strTable = pTable.Split('.');
        //Get data from source table
        srcConn.Open();                
        SqlCommand srcCommand = new SqlCommand("SELECT * FROM " + 
                                pTable, srcConn);
        srcCommand.CommandTimeout = 0;
        SqlDataReader sqldr = srcCommand.ExecuteReader();
        SqlBulkCopy sqlbcp = new SqlBulkCopy(pDestConn,
                             SqlBulkCopyOptions.KeepIdentity | 
                             SqlBulkCopyOptions.TableLock);

        //Map Columns
        SqlConnection destConn = new SqlConnection(pDestConn);
        destConn.Open();
        SqlCommand cmd1 = new SqlCommand("SELECT COLUMN_NAME," + 
                         "COLUMNPROPERTY(OBJECT_ID('" + 
                         strTable[0] +"." +strTable[1]+
                         "'),COLUMN_NAME,'IsComputed')AS 'IsComputed' " + 
                         "FROM INFORMATION_SCHEMA.COLUMNS " + 
                         "WHERE TABLE_SCHEMA = '" + 
                         strTable[0] + "' AND TABLE_NAME = '" + 
                         strTable[1] + "'", destConn);
        SqlDataReader drcolumns = cmd1.ExecuteReader();
        while (drcolumns.Read())
        {
            if(drcolumns.GetInt32(1) != 1)
            sqlbcp.ColumnMappings.Add(drcolumns.GetString(0), 
                                      drcolumns.GetString(0));
        }
        drcolumns.Close();

        //Load Data
        sqlbcp.BulkCopyTimeout = 0;                
        sqlbcp.DestinationTableName = pTable;
        sqlbcp.WriteToServer(sqldr);
        sqldr.Close();
    }
 }

Conclusion

That's all there is to it. I hope this application helps for migrating your data. This can be customized easily if you want to load data from different data sources (e.g.: XML files, flat files...). Thanks for reading. Please provide me feedback about issues or ways to improve.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

siva pinnaka
Web Developer
United States United States
MCSD.NET Early Achiever,MCDBA SQL 2000,MCITP SQL 2005 Charter Member,MCPD Charter Member

Comments and Discussions

 
GeneralMy vote of 5 PinmvpKanasz Robert24-Sep-12 5:54 
Generalcomputed column and sqlbulkcopy Pinmemberhavejeet8-Aug-09 2:21 
GeneralRe: computed column and sqlbulkcopy Pinmemberxx5824-Sep-09 21:56 
GeneralBulk Load Problem Pinmemberxx5814-Jul-09 22:44 
Generaljust what i needed, and worked nicely. PinmembersecretGeek_net11-Jan-09 13:25 
GeneralGreat Application, Siva PinmemberJames Bolles (elpaso)11-Sep-08 16:23 
GeneralEnforce Constraints [modified] Pinmemberspoekes9-Jul-08 23:08 
GeneralBackup and restore PinmemberRamsheer Pc1-Nov-07 20:01 
GeneralNice Article PinmemberHemant.Kamalakar22-Oct-07 1:51 
QuestionHow can I use SqlBulkCopy to copy data to the local temporary table which is dynamicly generated in a store procedure? Pinmemberdddd2181-Oct-07 5:13 
GeneralCollumn Exists in Dest but not Source. Pinmembertxghia581-Aug-07 9:46 
GeneralRe: Collumn Exists in Dest but not Source. Pinmembersiva pinnaka5-Aug-07 5:30 
GeneralLife Saver PinmemberJoeIsTheMan30-Jan-07 12:36 
GeneralRe: Life Saver Pinmembersiva pinnaka30-Jan-07 17:10 
GeneralUpdate to article Pinmembersiva pinnaka1-Dec-06 8:38 
GeneralRe: Update to article PinmemberJeff Firestone7-Dec-06 5:14 
GeneralRe: Update to article Pinmembersiva pinnaka7-Dec-06 6:52 
QuestionAlso create tables at destination? Pinmemberjackmos21-Nov-06 2:50 
AnswerRe: Also create tables at destination? Pinmembersiva pinnaka27-Nov-06 10:38 
QuestionThreads? Pinmemberxae19-Nov-06 9:45 
AnswerRe: Threads? Pinmembersiva pinnaka20-Nov-06 8:08 
GeneralVery good... PinmembervinSharp15-Nov-06 2:36 
GeneralRe: Very good... Pinmembersiva pinnaka15-Nov-06 3:43 
GeneralGood... PinmembervinSharp15-Nov-06 2:35 
GeneralHey Nice PinmemberKim Alexander7-Nov-06 8:12 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140821.2 | Last Updated 30 Nov 2006
Article Copyright 2006 by siva pinnaka
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid