How do I join tables across servers? 1

[partridge80]

Can anyone give me an overview of this please?

 

[TLGsoft]

You must link the remote servers to your server.
YourServer -> Security -> Linked Servers

then just do a

select a.*, b.* from Server1.myDatabase.dbo.Table1 as a, MyServer.AnotherDb.dbo.Table2 as b ON a.MyID = b.MyID

 

[JamesLean]

The easiest way is to set up linked servers.

eg, set server2 up as a linked server in server1 and then execute this type of query on server1:

SELECT * FROM table1 t1 JOIN server2.dbname.owner.table2 t2 ON t1.idcol = t2.idcol

Note how you just need to use the 4-part qualified name. You can find more information in BOL - look up "linked servers". --James

 

[tlbroadbent]

The query posted by TLGSoft will not work. It has incorrect syntax - a combination of old style and ANSI JOIN syntax.

JamesLean's query will work. If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[TLGsoft]

Yep, it won't work, but he got the ideea.