Exec problem in SQL Server 2005
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开发者_如何学Python. Make sure that the name is entered correctly.
Hope someone can tell me whot is wront with this.
Best Regards,
Iordan Tanev
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
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
精彩评论