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.

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

Featured Articles

 Site Author

  • Thanks for visiting!