Exec problem in SQL Server 2005

Question

Hi,

I have the situation where i have two databases with same structure. The first have some data in its data tables. I need to create a script that will transfer the data from the first database to the second. I have created this script.

DECLARE @table_name nvarchar(MAX),
        @query nvarchar(MAX)

DECLARE @table_cursor CURSOR 

SET @table_cursor = CURSOR FAST_FORWARD
FOR
    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

    OPEN @table_cursor
    FETCH NEXT FROM @table_cursor
    INTO @table_name

    WHILE @@FETCH_STATUS = 0
    BEGIN
       SET @query = 'INSERT INTO ' + @table_name + ' SELECT * FROM MyDataBase.dbo.' + @table_name   
       print @query
       exec @query

       FETCH NEXT FROM @table_cursor
       INTO @table_name
    END

    CLOSE @table_cursor
    DEALLOCATE @table_cursor 

The problem is that when I run the script the "print @query" statement prints statement like this

INSERT INTO table SELECT * FROM MyDataBase.dbo.table

When I copy this and run it from Management studio it works fine. But when the script tries to run it with exec I get this error

Msg 911, Level 16, State 1, Line 21
Could not locate entry in sysdatabases for database 'INSERT INTO table  SELECT * FROM MyDataBase'. No entry found with that name. Make sure that the name is entered correctly.

Hope someone can tell me whot is wront with this.

Best Regards,

Iordan Tanev

Answer 1

You could try and use the undocumented but extremely helpful sp_msForEachTable stored procedure to achieve this - instead of using a cursor to execute dynamic SQL.

In that case, you'd have a statement something like this:

exec sp_MSforeachtable @command1 = 'INSERT INTO ? SELECT * FROM MyDatabase.?'

where the ? would get replaced with the actual table name for each table in the database. This requires that the tables already exist in your target database.

Check out some resources on sp_msForEachTable for more details:

• SQL Server Undocumented Stored Procedures sp_MSforeachtable and sp_MSforeachdb
• 8 Common Uses of the undocumented Stored Procedure sp_MSforeachtable
• Using sp_msForeachTable
• The undocumented sp_MSforeachtable procedure

Answer 2

To solve your issue exactly...

You'd need to make it "dynamic SQL"

exec (@query)

EXEC @Query is actually EXEC @module_name_var with no brackets. See EXEC in MSDN. You want "Execute a character string" not "Execute a stored procedure or function"

However, I'd consider investing in something like Red Gate Compare bundle if it's something you need to do often