Statement does not execute when using the database name in a variable

Question

This statement works:

INSERT INTO TestDB2.dbo.MyTableName SELECT * FROM TestDB1.dbo2.MyTableName

This statement works not:

DECLARE @SourceDatabase varchar(50)
DECLARE @TargetDatabase varchar(50)

SET @SourceDatabase = 'TestDB1'    
SET @TargetDatabase = 'TestDB2' 

INSERT INTO @TargetDatabase.dbo.PrcConfiguration SELECT * FROM @SourceDatabase.sppm.CONFIGURATION

OR without the '@'

INSERT INTO TargetDatabase.dbo.PrcConfiguration SELECT * FROM SourceDatabase.dbo1.CONFIGURATION

dbo and dbo1 are the schema.

I want to move later data from multiple tables from one database to another database using the same tables.

What do I wrong?

Please read the standard page for dynamic SQL in SQL Server: sommarskog.se/dynamic_sql.html — Ocaso Protal
– Ocaso Protal, Commented Jan 12, 2012 at 9:01

Oleg Dok · Accepted Answer · 2012-01-12 09:10:51Z

1

You cannot pass the DB name in such a query in variable, use dynamic querying instead:

DECLARE @SourceDatabase varchar(50)
DECLARE @TargetDatabase varchar(50)
DECLARE @sql NVARCHAR(MAX)

SET @SourceDatabase = 'TestDB1'    
SET @TargetDatabase = 'TestDB2' 

SET @sql = N'INSERT INTO ['+@TargetDatabase+N'].dbo.PrcConfiguration SELECT * FROM ['+@SourceDatabase+N'].sppm.CONFIGURATION'

exec(@sql)

answered Jan 12, 2012 at 9:10

Oleg Dok

21.9k4 gold badges48 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Statement does not execute when using the database name in a variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related