3

I have 2 SQL servers. I need a SQL query that can join 2 tables that are in two different server.

Like 

SELECT  *
FROM    Server1.Db1.dbo.table1 A
        INNER JOIN Server2.Db1.dbo.table2 B ON A.Id = B.Id

and I do not have the server names, instead I am using IP address of the servers. Do I need to enable these SQL servers as linked server to allow such cross server queries?

AstroCB
  • 12,337
  • 20
  • 57
  • 73
Andy T
  • 1,355
  • 2
  • 19
  • 30
  • 1
    If these databases are on different servers then yes you need to create a linked server for this. – Sean Lange Aug 20 '14 at 14:15
  • 1
    You can use linked servers or OPENDATASOURCE. Be careful, these remote queries can get out of hand quickly. – sam yi Aug 20 '14 at 14:15
  • 2
    possible duplicate of [Cross-server SQL](http://stackoverflow.com/questions/70455/cross-server-sql) – Ajk_P Aug 20 '14 at 14:15
  • 1
    There is a post called: "Selecting data from two different servers in SQL Server" here in StackOverflow --> http://stackoverflow.com/q/1144051/771579 – Orlando Herrera Aug 20 '14 at 14:50

2 Answers2

7

You can proceed with Linked Servers using sp_addlinkedserver. Once done, you can query your data as you mentioned;

SELECT  *
FROM    [Db1].[dbo].table1 A
INNER JOIN [Server2].[Db1].[dbo].table2 B 
ON A.Id = B.Id
Nadeem_MK
  • 7,533
  • 7
  • 50
  • 61
1

Yes, add as linker server is one option. You also can join the remote table by use [ip address].dbname.dbo.table name s well.

Hlin
  • 134
  • 4