Copy Table Data Between Servers
-
Posted on January 13, 2014 by Derek Dieter
-
0
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]
- Comments (RSS)
- Trackback
- Permalink