In this example, I’m using Excel. In my list the SOURCE is the name of the table that we are copying from and the TARGET is where it’s being copied to.
In the SOURCE database, create the following table.
CREATE TABLE [dbo].[TRANSFER_TABLE_LIST](
[SOURCE_TABLE] [varchar](100),
[SOURCE_SCHEMA] [varchar](100),
[SOURCE_DATABASE] [varchar](100),
[TARGET_TABLE] [varchar](100),
[TARGET_SCHEMA] [varchar](100),
[TARGET_DATABASE] [varchar](100)
)
GO
Now, import your table list into the TRANSFER_TABLE_LIST table and create the following cursor.
DECLARE @SQL AS VARCHAR (2000), @SOURCE_TABLE AS VARCHAR (100),
@SOURCE_SCHEMA AS VARCHAR (100), @SOURCE_DATABASE AS VARCHAR (100),
@TARGET_TABLE AS VARCHAR (100), @TARGET_SCHEMA AS VARCHAR (100),
@TARGET_DATABASE AS VARCHAR (100)
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT [SOURCE_TABLE]
,[SOURCE_SCHEMA]
,[SOURCE_DATABASE]
,[TARGET_TABLE]
,[TARGET_SCHEMA]
,[TARGET_DATABASE]
FROM [dbo].[TRANSFER_TABLE_LIST]
OPEN c1
FETCH NEXT FROM c1
INTO @SOURCE_TABLE, @SOURCE_SCHEMA, @SOURCE_DATABASE, @TARGET_TABLE, @TARGET_SCHEMA, @TARGET_DATABASE
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'SELECT * INTO [' + @TARGET_DATABASE + '].[' + @TARGET_SCHEMA + '].['
SET @SQL = @SQL + @TARGET_TABLE + '] FROM [' + @SOURCE_DATABASE + '].['
SET @SQL = @SQL + @SOURCE_SCHEMA + '].[' + @SOURCE_TABLE + '];'
EXEC (@SQL)
FETCH NEXT FROM c1
INTO @SOURCE_TABLE, @SOURCE_SCHEMA, @SOURCE_DATABASE, @TARGET_TABLE, @TARGET_SCHEMA, @TARGET_DATABASE
END
CLOSE c1
DEALLOCATE c1
If you receive the following error:
Msg 203, Level 16, State 2, Line 23
The name 'SELECT * INTO [TransCorpReporting].[dbo].[Products_cust_trans] FROM [TransCorp].[dbo].[Products_cust_trans];' is not a valid identifier.
This means that you forgot to add ( ) around the @SQL variable.
No comments:
Post a Comment