How to query 2 identical SQL Server databases on different server machines

Question

I need to query to select full list from 2 remote databases which are structurally identical, but on different server machines.

How to use sp_addlinkedserver to query the 2 databases in the same query?

To get something like this each database requires the same user name and password

SELECT  
   [Pays]
FROM  
   /// [db1].[dbo].[liste_pays] Union [db1].[dbo].[liste_pays]///

They even have the same name but different data inside

Take a look at this http://stackoverflow.com/questions/1144051/selecting-data-from-two-different-servers-in-sql-server — g_tec, Apr 04 '14 at 16:03
Can you see both servers? I mean can you do SELECT [Pays] FROM [db1].[dbo].[liste_pays] and the same on other database? — ericpap, Apr 04 '14 at 16:03

score 0 · Answer 1 · answered Apr 04 '14 at 16:05

0

Click Here for a simple tutorial about how to create a linked server.

After creating linked server, we can query it as follows:

select * from LinkedServerName.DatabaseName.dbo.TableName

answered Apr 04 '14 at 16:05

Raging Bull

18,593
13
50
55

score 0 · Answer 2 · answered Apr 04 '14 at 16:07

You'll need to use sp_addlinkedserver to create a server link. See the reference documentation for usage. Once the server link is established, you'll construct the query as normal, just prefixing the database name with the other server. I.E:

Reference Docs : http://msdn.microsoft.com/en-us/library/ms190479.aspx

SELECT tab2.column_name  
FROM  [DB1.mdf].[dbo].[table_name_1] tab1 INNER JOIN [DB2.mdf].[dbo].[table_name_2]  tab2   
ON tab1.col_name = tab2.col_name

score 0 · Accepted Answer · answered Apr 04 '14 at 17:31

Suppose: You have a Server SR1, Database DB1 on Schema SC1 Table tbl1 and column Pays. You have a Server SR2, Database DB1 on Schema SC1 Table tbl1 and column Pays.

First you will need to add a linked server relationship between SR1 and SR2. Once that is established. You can query something like this:

SELECT ABC.Pays FROM SR1.DB1.SC1.Tbl1 ABC
UNION ALL
SELECT DEF.Pays FROM SR2.DB1.SC1.Tbl1 DEF

How to query 2 identical SQL Server databases on different server machines

3 Answers3