Click here to Skip to main content
11,721,289 members (83,775 online)
Click here to Skip to main content
Articles » Languages » C# » General » Revisions

Transfer Data or Script between two SQL Server Database

, 7 Nov 2012 CPOL 51.9K 2.2K 51
Rate this:
Please Sign up or sign in to vote.
Data Transfer tools who can transfer data and database script between two SQL Server database.
This is an old version of the currently published article.

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... 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

csharpbd
Software Developer
Bangladesh Bangladesh
"passion", "excitement" and "learning"

You may also be interested in...

Comments and Discussions


Discussions posted for the Published version of this article. Posting a message here will take you to the publicly available article in order to continue your conversation in public.
 
SuggestionMy vote of 2 Pin
phil.o6-Mar-15 17:35
professionalphil.o6-Mar-15 17:35 
Questioni does not understand the script code, please explain from beginner level , any tutorial Pin
Member 1068915421-Mar-14 8:58
memberMember 1068915421-Mar-14 8:58 
AnswerRe: i does not understand the script code, please explain from beginner level , any tutorial Pin
csharpbd21-Mar-14 12:04
membercsharpbd21-Mar-14 12:04 
QuestionAbout online server to server transfer? Pin
Momotaj17-Mar-14 5:21
memberMomotaj17-Mar-14 5:21 
AnswerRe: About online server to server transfer? Pin
csharpbd17-Mar-14 5:33
membercsharpbd17-Mar-14 5:33 
GeneralRe: About online server to server transfer? Pin
Momotaj17-Mar-14 5:40
memberMomotaj17-Mar-14 5:40 
QuestionGreat Pin
Member 368844520-Sep-13 11:58
memberMember 368844520-Sep-13 11:58 
AnswerRe: Great Pin
csharpbd22-Sep-13 19:52
membercsharpbd22-Sep-13 19:52 
GeneralMy vote of 5 Pin
kamrulTalukdar17-Jun-13 5:23
groupkamrulTalukdar17-Jun-13 5:23 
GeneralRe: My vote of 5 Pin
csharpbd17-Jun-13 5:36
membercsharpbd17-Jun-13 5:36 
GeneralMy vote of 5 Pin
Member 847743229-May-13 3:33
memberMember 847743229-May-13 3:33 
GeneralMy vote of 5 Pin
almamun199626-May-13 22:34
memberalmamun199626-May-13 22:34 
GeneralMy vote of 4 Pin
Srinivassurpur20-Mar-13 3:56
memberSrinivassurpur20-Mar-13 3:56 
QuestionGreat help Pin
zq_linda17-Jan-13 22:42
memberzq_linda17-Jan-13 22:42 
AnswerRe: Great help Pin
csharpbd19-Jan-13 5:41
membercsharpbd19-Jan-13 5:41 
GeneralMy vote of 5 Pin
Sanjay K. Gupta6-Dec-12 21:45
memberSanjay K. Gupta6-Dec-12 21:45 
GeneralRe: My vote of 5 Pin
csharpbd9-Dec-12 16:51
membercsharpbd9-Dec-12 16:51 
GeneralMy vote of 5 Pin
Olivier Giulieri20-Nov-12 20:30
memberOlivier Giulieri20-Nov-12 20:30 
GeneralRe: My vote of 5 Pin
csharpbd20-Nov-12 21:03
membercsharpbd20-Nov-12 21:03 
GeneralMy vote of 5 Pin
ZigZagCode18-Nov-12 5:11
memberZigZagCode18-Nov-12 5:11 
GeneralMy vote of 4 Pin
Jasmine250112-Nov-12 10:39
memberJasmine250112-Nov-12 10:39 
GeneralRe: My vote of 4 Pin
csharpbd13-Nov-12 7:20
membercsharpbd13-Nov-12 7:20 
GeneralRe: My vote of 4 Pin
Jasmine250113-Nov-12 7:45
memberJasmine250113-Nov-12 7:45 
GeneralMy vote of 5 Pin
Anurag Gandhi12-Nov-12 7:44
memberAnurag Gandhi12-Nov-12 7:44 
GeneralRe: My vote of 5 Pin
csharpbd12-Nov-12 7:55
membercsharpbd12-Nov-12 7:55 

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 | Terms of Use | Mobile
Web02 | 2.8.150901.1 | Last Updated 7 Nov 2012
Article Copyright 2012 by csharpbd
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid