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 MPDEV090314'. 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