Stop the Hollyweb! No DRM in HTML5.   

Wednesday, June 10, 2009

Copy Several Tables to a Different Database Using Dynamic SQL

Here is a quick example of how to used dynamic SQL in a cursor to copy several tables from one database to another. I was given a list of 150 tables that one of my customers needed to be copied. I didn’t want to script all of them so I created this cursor. All you need to do is create a list of the tables to be copied.

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