Join 2 tables from different databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Join 2 tables from different databases

Author

Topic

Razzle00
Starting Member

35 Posts

Posted - 2007-05-23 : 08:20:40

Hi,

How do you join 2 tables from 2 different databases? Do you need 2 seperate connection strings for each database? Any examples would be appreciated. I am using SQL 2005 Standard Edition.

Thanks,

Razzle

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-23 : 08:23:45

use the 3 part names to reference the table


select *
from   db1.dbo.table1 a inner join db2.dbo.table2 b
on     a.col = b.col

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-05-23 : 09:59:18

Also, if they're not on the same server, you need to either (a)create a linked server or (b)use an ad hoc query, using the OPENDATASOURXE function (search books online).

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-23 : 10:28:56

<<
OPENDATASOURXE
>>

thats OPENDATASOURCE

Madhivanan

Failing to plan is Planning to fail

Razzle00
Starting Member

35 Posts

Posted - 2007-05-23 : 11:47:49

Thanks for the replies. I got the query working by making sure the permissions for each table in each different database could be selected from the same user. Then using the 3 part names to reference the table worked.

I will check out the OPENDATASOURCE function. That looks like something I could definately use.

pvedi
Starting Member

6 Posts

Posted - 2007-05-23 : 12:24:19

How about creating a synonym?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources