asked on 12/30/2014

How do I write a series of SQL Server SELECT statements that compare tables in two databases with variables for the db names?

I want to write something like this

declare @db1 nvarchar(100);
declare @db2 nvarchar(100);

select id from [@db1]..MyTable t1 INNER JOIN [@db2]..MyTable t2 on [t1].id=[t2].id

but clearly SQL Server doesn't like the database names in variables - how can I achieve what I am trying to do?

I'm on but I'd like it as generic as possible for versions:
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
      May 14 2014 18:34:29
      Copyright (c) Microsoft Corporation
      Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

Microsoft SQL Server 2008 Microsoft SQL Server Microsoft SQL Server 2005

Last Comment

purplesoup

12/30/2014

ASKER CERTIFIED SOLUTION

Vikas Garg

12/30/2014

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Guy Hengel [angelIII / a3]

12/30/2014

I concur you cannot do without going dynamic sql ( or going ad-hoc sql from outside the sql as such)
you cannot use variables for any of the following : database, table, column names etc ...

purplesoup

12/30/2014

ASKER

Ok thanks - when I saw the answer I guessed there wasn't going to be some more useful way to do it. I'm supplying a script to someone and it would have been useful for them to just set the names of the databases at the top of the script, but I think I'm just going to tell them to do a find and replace on the database names, going the dynamic sql route is going to ruin readability.