Copy Table Data Between Servers

This script will copy table data from one environment to another. This script uses a multithreaded clr procedure created by Alan Kaplan here.

You can also change the parallel call below if you want to execute serially.

[cc lang=”sql”]

CREATE PROCEDURE [dba].[pr_refresh_insert_table_data]
@linked_server varchar(100),
@source_db varchar(100),
@target_db varchar(100)
AS
— dba.pr_refresh_insert_table_data ‘10.10.60.58’, ‘yourdbname’, ‘targetdb’
— SELECT * FROM admindb.dbo.refresh_table_columns
BEGIN
SET NOCOUNT ON;

DECLARE @SQL nvarchar(max);
DECLARE @SQLColumnList nvarchar(max);
DECLARE @SQLSelectColumnList nvarchar(max);
DECLARE @CRLF nvarchar(2);
DECLARE @Tab nvarchar(1);
DECLARE @RecordKeyName nvarchar(50);
DECLARE @TableID int;
DECLARE @TableCount int;
DECLARE @source varchar(max) = ‘[‘ + @linked_server + ‘].[‘ + @source_db + ‘]’

DECLARE @TableName varchar(255);

SET @TableName = ”; — Comment out to use list technique below

DECLARE @TableList TABLE (
TableID int IDENTITY(1, 1) NOT NULL,
TableName varchar(255)
);

DECLARE @InsertData Generic_Tt_VarcharMax_5_Col

INSERT INTO @TableList(TableName)
SELECT DISTINCT table_name
FROM admindb.[tmp].[refresh_table_schema_list] tsl
WHERE process_data = 1
AND EXISTS (SELECT 1 FROM admindb.tmp.refresh_create_tables WHERE table_name = tsl.table_name)

SET @CRLF = CHAR(13) + CHAR(10);
SET @Tab = CHAR(9);

SET @TableCount = (SELECT COUNT(1) FROM @TableList);
SET @TableID = 1;

DECLARE @has_identity int

SET @TableID = 1;
WHILE @TableID <= @TableCount BEGIN SET @TableName = (SELECT TableName FROM @TableList WHERE TableID = @TableID); SET @has_identity = (SELECT CASE WHEN EXISTS (SELECT 1 FROM admindb.dbo.refresh_table_columns WHERE IsIdentity = 1 AND TableName = @TableName) THEN 1 ELSE 0 END) SET @SQL = '-- Target Table ' + @CRLF + 'IF EXISTS (SELECT * FROM ' + @target_db + '.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''' + @TableName + ''') ' + @CRLF + 'BEGIN ' + @CRLF + (CASE WHEN @has_identity = 1 THEN + 'SET IDENTITY_INSERT ' + @target_db + '.[dbo].[' + @TableName + '] ON' + @CRLF ELSE '' END) + @Tab + 'INSERT INTO ' + @target_db + '.[dbo].[' + @TableName + '] ( ' + @CRLF; SELECT @SQL = @SQL + @Tab + @Tab + '[' + ColumnName + '] ' + (CASE WHEN IsLast = 1 THEN @CRLF + ') ' ELSE ', ' END) + @CRLF FROM admindb.dbo.refresh_table_columns WHERE TableName = @TableName ORDER BY RowID; SET @SQL = @SQL + 'SELECT ' + @CRLF SELECT @SQL = @SQL + @Tab + @Tab + '[' + ColumnName + '] ' + (CASE WHEN IsLast = 1 THEN @CRLF + '' ELSE ', ' END) + @CRLF FROM admindb.dbo.refresh_table_columns WHERE TableName = @TableName ORDER BY RowID; SET @SQL = @SQL + ' FROM ' + @source + '.[dbo].[' + @TableName + '] WITH (NOLOCK)' + @Tab + @Tab + @CRLF + + (CASE WHEN @has_identity = 1 THEN ' SET IDENTITY_INSERT ' + @target_db + '.[dbo].[' + @TableName + '] OFF' ELSE '' END) + @CRLF + 'END ' + @CRLF INSERT INTO @InsertData (column1, column2) SELECT @TableName, @SQL --EXEC (@SQL); SET @TableID = @TableID + 1; END END --select * from @InsertData -- Execute and get the CDC Changes EXEC admindb.Dba.spParallelExecute 10 -- Concurrent Threads ,0 -- Command Timeout. 0 = None ,'Insert_data' -- Block Name ,'ReadUncommitted' -- Transaction Isolation ,@InsertData -- Table of Execution Statements ,0 [/cc]