February 26, 2008 at 12:47 pm
Hello everyone,
In a T-SQL statement can you join tables if they reside on different servers? I can do it if the tables are in different databases on the same server, but I haven't found a way to do it if the tables are in a different database on a different server. If this can be done, does anyone know the syntax? Or even a sample SQL statement?
Thanks,
Strick
February 26, 2008 at 1:00 pm
You have to make the remote server a linked server. You can expand the server objecsts folder in SSMS and add a new linked server.
You have to use the four part name to reference a linked server.
linkedserver.catalog.schema.object_name
February 26, 2008 at 1:05 pm
You'd want to look up two separate concepts (different sides of the same coin).
If you plan on doing this a lot - you want to set up what is called a Linked Server. Meaning - you essentially "introduce" a remote server to the local server. Once you do - based on the security mapping in the linked server - you can then incorporate data from the remote server in your local query.
If you do this ad-hoc or once in a while - look up the OPENDATASOURCE/OPENROWSET predicates, essentially allowing you to set up a temporary link, which will disappear when it goes out of scope.
Once you set up either, you can launch queries looking like the following (assuming a linked server in this example):
select a.*, b.*
from
MyTable a
inner join MyLinkedServer.MyremoteDB.owner.MyTable b
on a.id=b.id
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply