Sometimes we need to transfer database data and objects from one server to another. In this article I represent a tool which can transfer data and database objects like tables and stored procedure scripts to another SQL Server database. Practically I've transferred database objects from SQL Server 2005 to SQL Server 2012. Then I thought it could help other stuff which would face these kinds of problems!
Background
Once I had a requirement to transfer data between two online databases. I saw many tools on the internet for transferring data between two SQL Server databases, but I decided to develop this kind of a tool because I believed that if you write some code you learn something new...
In this article we learn the following points:
- How to connect to a SQL Server database.
- How to generate a Table and SP script programmatically.
- How to copy data between two tables using Bulk Copy.
- How to insert data in an Identity column manually.
Using the code
The name of this project is DataTransfer. I use Windows Forms Application in Visual Studio 2008 to developed this project. I separate three sections to design the UI. See the image below pointing out the five main functionalities:
1. Point-1 in section-1: this section is used to take the source server information. Source server means a SQL Server database that has some data and objects needed to transfer.
2. Point-2 in section -2: this section is used to take the destination server information. Destination server means a SQL Server database where we place transferable objects and data.
3. Point-3 in sections 1 and 2: used 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 the first time we build the source and destination server connection considering the 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";
}
}
1. Point-4 in section-3: used to transfer behavior. There are two main options: one for Table object and another for Store Procedure object. When we select a table, the text box prepares to get transferable table name, and when we select a SP then the text box prepares to get the SP name.
2. Point-5 in section-3: used mainly when we try to transfer a Table object from a database to another database. When we transfer a Table then there are two transferable options: a table script and table data. This application creates a table script from a source database and executes this script to create a table in a destination database.
To create a table script we use a T-SQL statement. When we execute this T-SQL statement it returns a datatable with some rows. Those rows have a total table script with Identity, and a 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 a SP script we use a SQL Server database built-in Sp name "sp_helptext", it has a parameter to get the 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 scripts from the source database then simply execute the destination database for transferring both objects to another database.
Now we transfer data between two servers. In this project we use two options to transfer data: Bulk copy method, or generate an insert statements according to source table and data then execute those statements in the 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 statements 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 second data copy option because when a table has an Identity column and we try to copy data from another table, the new table generates a new ID for the identity column and not use the existing identity column data. For this situation we use this option to copy data. Also we use an extra statement to insert an identity column value before we execute an insert statement in the destination server, and after we execute the insert statement, we need to execute another statement.
Identity column code:
SET IDENTITY_INSERT [" + txtTableName.Text + "] ON // before execute insert statement
SET IDENTITY_INSERT [" + txtTableName.Text + "] OFF // after execute insert statement