How do you join tables from two different SQL Server instances in one SQL query

sql-server join multiple-instances
Tarik picture Tarik · Sep 14, 2009 · Viewed 112.5k times · Source

Possible Duplicate:
Selecting data from two different servers in SQL Server

How can I join two tables, which are located two different SQL Server instances, in one query?

Answer

Scott Anderson picture Scott Anderson · Sep 14, 2009

The best way I can think of to accomplish this is via sp_addlinkedserver. You need to make sure that whatever account you use to add the link (via sp_addlinkedsrvlogin) has permissions to the table you're joining, but then once the link is established, you can call the server by name, i.e.:

SELECT *
FROM server1table
    INNER JOIN server2.database.dbo.server2table ON .....

Related questions

SQL update from one Table to another based on a ID match

I have a database with account numbers and card numbers. I match these to a file to update any card numbers to the account number, so that I am only working with account numbers. I created a view linking the …

Read More
Update a table using JOIN in SQL Server?

I want to update a column in a table making a join on other table e.g.: UPDATE table1 a INNER JOIN table2 b ON a.commonfield = b.[common field] SET a.CalculatedColumn= b.[Calculated Column] WHERE b.[common field]= …

Read More
Difference between JOIN and INNER JOIN

Both these joins will give me the same results: SELECT * FROM table JOIN otherTable ON table.ID = otherTable.FK vs SELECT * FROM table INNER JOIN otherTable ON table.ID = otherTable.FK Is there any difference between the statements in performance …

Read More