Click here to Skip to main content
12,999,010 members (51,952 online)
Click here to Skip to main content
Add your own
alternative version


88 bookmarked
Posted 24 Oct 2006

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.


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<?xml:namespace prefix = o /><o:p>
  • Reports progress as it loads data<o:p>
  • Logs results of load or delete operations for every table<o:p>
  • 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.


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))
        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";

            //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)
                        cmd.CommandText = 
                         "SELECT OBJECTPROPERTY ( object_id('" + 
                         dr.ToString() + "'),'TableHasForeignRef')";
                        int intref = Convert.ToInt32(cmd.ExecuteScalar());
                        if (intref == 1)
                            cmd.CommandText = "DELETE FROM " + dr.ToString();
                            cmd.CommandText = "TRUNCATE TABLE " + 

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

            //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";


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
        SqlCommand srcCommand = new SqlCommand("SELECT * FROM " + 
                                pTable, srcConn);
        srcCommand.CommandTimeout = 0;
        SqlDataReader sqldr = srcCommand.ExecuteReader();
        SqlBulkCopy sqlbcp = new SqlBulkCopy(pDestConn,
                             SqlBulkCopyOptions.KeepIdentity | 

        //Map Columns
        SqlConnection destConn = new SqlConnection(pDestConn);
        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)

        //Load Data
        sqlbcp.BulkCopyTimeout = 0;                
        sqlbcp.DestinationTableName = pTable;


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.


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


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

You may also be interested in...


Comments and Discussions

GeneralMy vote of 5 Pin
Kanasz Robert24-Sep-12 5:54
mvpKanasz Robert24-Sep-12 5:54 
Generalcomputed column and sqlbulkcopy Pin
havejeet8-Aug-09 2:21
memberhavejeet8-Aug-09 2:21 
GeneralRe: computed column and sqlbulkcopy Pin
xx5824-Sep-09 21:56
memberxx5824-Sep-09 21:56 
GeneralBulk Load Problem Pin
xx5814-Jul-09 22:44
memberxx5814-Jul-09 22:44 
Generaljust what i needed, and worked nicely. Pin
secretGeek_net11-Jan-09 13:25
membersecretGeek_net11-Jan-09 13:25 
GeneralGreat Application, Siva Pin
James Bolles (elpaso)11-Sep-08 16:23
memberJames Bolles (elpaso)11-Sep-08 16:23 
GeneralEnforce Constraints [modified] Pin
spoekes9-Jul-08 23:08
memberspoekes9-Jul-08 23:08 
GeneralBackup and restore Pin
Ramsheer Pc1-Nov-07 20:01
memberRamsheer Pc1-Nov-07 20:01 
GeneralNice Article Pin
Hemant.Kamalakar22-Oct-07 1:51
memberHemant.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? Pin
dddd2181-Oct-07 5:13
memberdddd2181-Oct-07 5:13 
GeneralCollumn Exists in Dest but not Source. Pin
txghia581-Aug-07 9:46
membertxghia581-Aug-07 9:46 
GeneralRe: Collumn Exists in Dest but not Source. Pin
siva pinnaka5-Aug-07 5:30
membersiva pinnaka5-Aug-07 5:30 
GeneralLife Saver Pin
JoeIsTheMan30-Jan-07 12:36
memberJoeIsTheMan30-Jan-07 12:36 
GeneralRe: Life Saver Pin
siva pinnaka30-Jan-07 17:10
membersiva pinnaka30-Jan-07 17:10 
GeneralUpdate to article Pin
siva pinnaka1-Dec-06 8:38
membersiva pinnaka1-Dec-06 8:38 
GeneralRe: Update to article Pin
Jeff Firestone7-Dec-06 5:14
memberJeff Firestone7-Dec-06 5:14 
GeneralRe: Update to article Pin
siva pinnaka7-Dec-06 6:52
membersiva pinnaka7-Dec-06 6:52 
QuestionAlso create tables at destination? Pin
jackmos21-Nov-06 2:50
memberjackmos21-Nov-06 2:50 
AnswerRe: Also create tables at destination? Pin
siva pinnaka27-Nov-06 10:38
membersiva pinnaka27-Nov-06 10:38 
QuestionThreads? Pin
xae19-Nov-06 9:45
memberxae19-Nov-06 9:45 
AnswerRe: Threads? Pin
siva pinnaka20-Nov-06 8:08
membersiva pinnaka20-Nov-06 8:08 
GeneralVery good... Pin
vinSharp15-Nov-06 2:36
membervinSharp15-Nov-06 2:36 
GeneralRe: Very good... Pin
siva pinnaka15-Nov-06 3:43
membersiva pinnaka15-Nov-06 3:43 
GeneralGood... Pin
vinSharp15-Nov-06 2:35
membervinSharp15-Nov-06 2:35 
GeneralHey Nice Pin
Kim Alexander7-Nov-06 8:12
memberKim Alexander7-Nov-06 8:12 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170622.1 | Last Updated 30 Nov 2006
Article Copyright 2006 by siva pinnaka
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid