Transfer Data or Script between two SQL Server Database

• Download source - 39 KB

Introduction

Sometime we need to transfer database data and object one to another server. In this article i represent a tools who can transfer data and database object like table and store procedure script one to another SQL Server database. Practically I've transferred database objects from SQL Server 2005 to SQL Server 2012. Then I though it could help other stuff who would face this kind of problems!   

Background 

Once i have a requirement to transfer data between two online database. in internet i saw many tools for transfer data between two sql server database, but i decided to develop this kind of tools because  i believe that if you write some code you must be learn some new... 

In this article we learn following point: 

• How to connect SQL Server database. 
• How to generate Table and SP Script programmatically. 
• How to copy data between two Table using Bulk copy.
• How to insert data in Identity column manually.  

Using the code 

The name of this project DataTransfer. I use WindoesFormApplication in Visual Studio 2008 to developed this project. i separated 3 section to design UI see image bellow also pointing 5 main functionality.

1. Point-1 in section-1 this section use to take source server information. Source server means a SQL Server database there have some data and object need to transfer.

2. Point-2 in section -2 this section use to take destination server information. Destination server means a SQL Server database there we placed transferable object and data.

3. Point-3 in section-1 & 2 it's use to set sql server connection authentication because  most of the time when we connect a database we use SQL Server Authentication or Windows Authentication.

When we move to transfer an object or some data 1st time we build Source and Destination server connection considering above point.   

Connection String Build Code:

public void BuildConnectionString()
        {
            if (chkSAuthentication.Checked)
            {
                strSrc = "Data Source=" + txtSServerName.Text + ";Initial Catalog=" + txtSDatabase.Text + ";User Id=" + txtSLogin.Text + ";Password=" + txtSPassword.Text;
            }
            else
            {
                strSrc = "Data Source=" + txtSServerName.Text + ";Initial Catalog=" + txtSDatabase.Text + ";Integrated Security=True";
            }

            if (chkDAuthentication.Checked)
            {
                strDst = "Data Source=" + txtDServerName.Text + ";Initial Catalog=" + txtDDatabase.Text + ";User Id=" + txtDLogin.Text + ";Password=" + txtDPassword.Text;
            }
            else
            {
                strDst = "Data Source=" + txtDServerName.Text + ";Initial Catalog=" + txtDDatabase.Text + ";Integrated Security=True";
            }
        }  

4. Point-4 in section-3 it use to transfer behavior. there two main option one for Table object and another one Store Procedure object. when select Table then text box prepare to get transferable Table name and when select SP then text box prepare to get SP name.

5. Point-5 in section-3 it use mainly when we try to transfer Table object from a database to another database. When we transfer a Table then there are two transferable option one Table script one Table data. this application create Table script from source database and execute this script to create a Table in destination database. 

To create Table script we use T-SQL statement, when we execute this T-SQL statement it return a datatable with some rows. Those rows have total Table script with Identity, Primary key script.

Table Script Generate Code: 

public string GetTableScript(string TableName, string ConnectionString)
        {
            string Script = "";

            string Sql = "declare @table varchar(100)" + Environment.NewLine +
            "set @table = '" + TableName + "' " + Environment.NewLine +
                //"-- set table name here" +
            "declare @sql table(s varchar(1000), id int identity)" + Environment.NewLine +
            " " + Environment.NewLine +
                //"-- create statement" +
            "insert into  @sql(s) values ('create table [' + @table + '] (')" + Environment.NewLine +
            " " + Environment.NewLine +
                //"-- column list" +
            "insert into @sql(s)" + Environment.NewLine +
            "select " + Environment.NewLine +
            "    '  ['+column_name+'] ' + " + Environment.NewLine +
            "    data_type + coalesce('('+cast(character_maximum_length as varchar)+')','') + ' ' +" + Environment.NewLine +
            "    case when exists ( " + Environment.NewLine +
            "        select id from syscolumns" + Environment.NewLine +
            "        where object_name(id)=@table" + Environment.NewLine +
            "        and name=column_name" + Environment.NewLine +
            "        and columnproperty(id,name,'IsIdentity') = 1 " + Environment.NewLine +
            "    ) then" + Environment.NewLine +
            "        'IDENTITY(' + " + Environment.NewLine +
            "        cast(ident_seed(@table) as varchar) + ',' + " + Environment.NewLine +
            "        cast(ident_incr(@table) as varchar) + ')'" + Environment.NewLine +
            "    else ''" + Environment.NewLine +
            "   end + ' ' +" + Environment.NewLine +
            "    ( case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + " + Environment.NewLine +
            "    coalesce('DEFAULT '+COLUMN_DEFAULT,'') + ','" + Environment.NewLine +
            " " + Environment.NewLine +
            " from information_schema.columns where table_name = @table" + Environment.NewLine +
            " order by ordinal_position" + Environment.NewLine +
            " " + Environment.NewLine +
                //"-- primary key" +
            "declare @pkname varchar(100)" + Environment.NewLine +
            "select @pkname = constraint_name from information_schema.table_constraints" + Environment.NewLine +
            "where table_name = @table and constraint_type='PRIMARY KEY'" + Environment.NewLine +
            " " + Environment.NewLine +
            "if ( @pkname is not null ) begin" + Environment.NewLine +
            "    insert into @sql(s) values('  PRIMARY KEY (')" + Environment.NewLine +
            "    insert into @sql(s)" + Environment.NewLine +
            "        select '   ['+COLUMN_NAME+'],' from information_schema.key_column_usage" + Environment.NewLine +
            "        where constraint_name = @pkname" + Environment.NewLine +
            "        order by ordinal_position" + Environment.NewLine +
                //"    -- remove trailing comma" +
            "    update @sql set s=left(s,len(s)-1) where id=@@identity" + Environment.NewLine +
            "    insert into @sql(s) values ('  )')" + Environment.NewLine +
            "end" + Environment.NewLine +
            "else begin" + Environment.NewLine +
                //"    -- remove trailing comma" +
            "    update @sql set s=left(s,len(s)-1) where id=@@identity" + Environment.NewLine +
            "end" + Environment.NewLine +
            " " + Environment.NewLine +
            "-- closing bracket" + Environment.NewLine +
            "insert into @sql(s) values( ')' )" + Environment.NewLine +
            " " + Environment.NewLine +
                //"-- result!" +
            "select s from @sql order by id";

            DataTable dt = GetTableData(Sql, ConnectionString);
            foreach (DataRow row in dt.Rows)
            {
                Script += row[0].ToString() + Environment.NewLine;
            }

            return Script;
        } 

To create SP script we use SQL Server database built in sp name "sp_helptext" it have a parameter to get sp name.

SP Script Generate Code:

public string GetSPScript(string SPName, string ConnectionString)
        {
            string Script = "";

            string Sql = "sp_helptext '" + SPName + "'";

            DataTable dt = GetTableData(Sql, ConnectionString);
            foreach (DataRow row in dt.Rows)
            {
                Script += row[0].ToString() + Environment.NewLine;
            }

            return Script;
        } 

When we get both script from source database then simply it execute destination database for transfer both object one to another database.

Now we transfer data between two server. In this project we use two option to transfer data one to another server. One  Bulk copy method another one generate insert statement according to source Table and Data then execute those statement to destination server. 

Bulk Data Copy Code:

void TransferData()
        {
            try
            {
                DataTable dataTable = new Utility().GetTableData("Select * From " + txtTableName.Text, strSrc);

                SqlBulkCopy bulkCopy = new SqlBulkCopy(strDst, SqlBulkCopyOptions.TableLock)
                {
                    DestinationTableName = txtTableName.Text,
                    BatchSize = 100000,
                    BulkCopyTimeout = 360
                };
                bulkCopy.WriteToServer(dataTable);

                MessageBox.Show("Data Transfer Succesfull.");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }   

Copy Data using Insert Statement Code:  

void TransferDataWithTableScript()
        {
            try
            {
                
                DataTable dataTable = new Utility().GetTableData("Select * From " + txtTableName.Text, strSrc);

                if (!string.IsNullOrEmpty(new Utility().GetIdentityColumn(txtTableName.Text, strSrc)))
                {
                    string InsertSQL = "";
                    InsertSQL += "SET IDENTITY_INSERT [" + txtTableName.Text + "] ON " + Environment.NewLine;

                    string ColumnSQL = "";
                    foreach (DataColumn column in dataTable.Columns)
                    {
                        ColumnSQL += column.ColumnName + ",";
                    }
                    ColumnSQL = ColumnSQL.Substring(0, ColumnSQL.Length - 1);

                    foreach (DataRow row in dataTable.Rows)
                    {
                        string ColumnValueL = "";
                        foreach (DataColumn column in dataTable.Columns)
                        {
                            ColumnValueL += "'" + row[column.ColumnName].ToString().Replace("''", "'") + "',";
                        }
                        ColumnValueL = ColumnValueL.Substring(0, ColumnValueL.Length - 1);

                        InsertSQL += "Insert Into " + txtTableName.Text + " (" + ColumnSQL + ") Values(" + ColumnValueL + ")" + Environment.NewLine;
                    }

                    InsertSQL += "SET IDENTITY_INSERT [" + txtTableName.Text + "] OFF " + Environment.NewLine;

                    new Utility().ExecuteQuery(InsertSQL, strDst);
                }
                
                MessageBox.Show("Data Transfer Succesfull.");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }  

We need to use data copy 2nd option  because a table have Identity column and we try to copy data from another table the new table generate new id for identity column except existing identity column data. For this situation we use this option to copy data one to another database table. also there use an extra statement to insert identity column value before execute insert statement in destination server and after execute insert statement need to execute another one statement. 

Identity Column Code:  

SET IDENTITY_INSERT [" + txtTableName.Text + "] ON // before execute insert statement 

SET IDENTITY_INSERT [" + txtTableName.Text + "] OFF // after execute insert statement   

Points of Interest  

I learn from this project Bulk copy method, Table and Store Procedure script generation. 

Sorry for my bad English...