Monday, March 12, 2012

Multiple databases and SQL

I am facing a problem within SQL server. Is it possible to create SQL query to two separate databases at the same time ?

There are logical relations between tables in separate databases and I need to create some JOINs between tables in separate dbs into my SQL query. I there any solutions to this problem ?is the databases are on the same server you just need to use a three part nameing convention <Database Name>.<Owner Name>.<Table Name>

So if in db1 you have a table named tbl1 owned by usr1 and in db2 you have a table named tbl2 owned by dbo you would...

select *
from db1.usr1.tbl1 t1
join db2.dbo.tbl2 t2 on t1.ParentKey = t2.ChildKey|||On the same server?
If so, just do a
select * from database1.owner1.table1
join database2.owner2.table2 on table1.field1=table2.field2

If the're on different servers (but still mssql) then use a linked server (heterogenous join)
select * from server1.database1.owner1.table1
join server2.database2.owner2.table2 on table1.field1=table2.field2

If it's a different dbms, use openquery or openrowset (depending on what exactly you're trying to do)

Originally posted by asset1
I am facing a problem within SQL server. Is it possible to create SQL query to two separate databases at the same time ?

There are logical relations between tables in separate databases and I need to create some JOINs between tables in separate dbs into my SQL query. I there any solutions to this problem ?

No comments:

Post a Comment