Pump data from one server to another Efficiently

Sometimes the requirement comes when we need to pump the data from one server to another server.

Though we have SQL Import\Export wizard which can do this quiet easily, but in that way we will loose some important features of the table --

     1. The Identity property of a column in case we have one in the table.

     2. All the indexes.

To avoid this we can go with a simple SSIS package to which we just need to pass the table name as a parameter and it will do the rest, taking care of the above constraints.

This is how the Package looks -

Steps -

1. To make the executable dynamic we will drive the package from a table, where we will    store the names of all the tables which we want to refresh from the source server and we will store it in a result set variable (Table_List)

Refer the variables list in the below figure used in the package.

2. Create a For each loop container which will iterate through each item in the result stored in the Table_List variable.

3. Assign the value from the result set to the variable TableName for each iteration.

4. Now inside the loop configure each of the execute SQL task as below -

5. Check the existence of the table in the destination server.

6. If the table is not there, Use a Execute SQL Task which creates the query of create table from the source server and stores it in a variable.

Below is the query which will give you the DDL for a creating the table in destination from the server.

declare @vsSQL varchar(8000)

declare @vsTableName varchar(50)

set @vsTableName = 'Your Table Name'

select @vsSQL = 'CREATE TABLE ' + @vsTableName + char(10) + '(' + char(10)

select @vsSQL = @vsSQL + ' ' + sc.Name + ' ' +

st.Name +

case when st.Name in ('varchar','varchar','char','nchar') then '(' + cast(sc.Length as varchar) +') ' else ' ' end +

case when sc.IsNullable = 1 then 'NULL' else 'NOT NULL' end + ',' + char(10)

from sysobjects so

join syscolumns sc on sc.id = so.id

join systypes st on st.xusertype = sc.xusertype

where so.name = @vsTableName

order by

sc.ColID

select substring(@vsSQL,1,len(@vsSQL) - 2) + char(10) + ')'

7. Now use another Execute SQL Task to dump the data from Source to Destination.

8. you can now extract the index scripts on the table easily from the source server and execute them into the Destination server in another Execute SQL Task at the end.

Thanks for Reading !! Hope it helped you.