How to use variable in target database name for insert statement?

Question

I want to declare a server name and use this name in an insert statement. So far all i got is an error message.

declare @machine nvarchar(6);
declare @bar nvarchar(3);
set @machine = 'Name00';
set @bar = 'foo'

insert into @machine.dbname.dbo.table (column1, column2)
select (column1, column2)
from table
where column1 = @bar

This gives me:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '.'.

Answer 1

You will have to use dynamic sql for this. Try the following:

declare @machine nvarchar(6);
declare @bar nvarchar(3);
declare @query nvarchar(4000)


set @machine = 'Name00';
set @bar = 'foo'

set @query = 'insert into ' + @machine + '.dbname.dbo.table (column1, column2) select (column1, column2) from table where column1 = ''' + @bar + ''''

execute dbo.sp_executesql @query

Answer 2

I've had this problem before and the only work around I found is dynamic sql

declare @machine nvarchar(6)
declare @bar nvarchar(3)
declare @sql varchar(2000)
set @machine = 'Name00'
set @bar = 'foo'


Set @sql ='insert into ' + @machine + '.dbname.dbo.table (column1, column2)
select (column1, column2)
from table
where column1 = ''' + @bar + ''''

--print (@sql)
exec (@sql)

Answer 3

If you need to insert into different databases from the same source database, use dynamic SQL just as others say.

However, if the problem is that you want to syncronize devserver1 with devserver2, and prodserver1 with prodserver2, and have the other database as a config option, then use synonyms.