0

I have 3 separate tables which located in 3 separate servers(SQL Server Management Studio). Each table has a unique ID(int) column but for my next project I have to connect all those 3 tables into one. Before that I would like to know are there any overlapping ID's in any of those tables.

Ex : Server1 => DB1 => Table1 => IDs might be 1,2,3,4,5,6....
     Server2 => DB2 => Table2 => IDs might be 50,51,52,6,54....
     Server3 => DB3 => Table3 => IDs might be 89,90,2,92,93....

As in the example '6' is repeating in Server1 and Server2. Also '2' is repeating in Server1 and Server3. So my goal is to identify those ID's since each table has 50,000+ records it's harder to do manually.

I tried couple of methods as in below snippets by looking at some similar issues but none of them worked for me and ended up having "Invalid object name 'DB2.dbo.Table2'." error code

Method 1

select ID from [DB1].[dbo].[Table1]
intersect
select ID from [DB2].[dbo].[Table2]
intersect
select ID from [DB3].[dbo].[Table3]

Method 2

SELECT * 
FROM [DB1].[dbo].[Table1] tab1 
INNER JOIN [DB2].[dbo].[Table2] tab2 
ON tab1.ID = tab2.ID
underscore_d
  • 6,309
  • 3
  • 38
  • 64
MegaTrone
  • 27
  • 7
  • Of course, if you don't specify the server name, SQL will only look on the server you're currently connected to... You need to set up liked servers and then select like `[LinkedServerName].[DatabaseName].[SchemaName].[TableName]`. There should be guides to doing that available on SO or elsewhere. – underscore_d May 10 '21 at 13:36
  • @underscore_d thank you for the information, I will try this out. One last question before I touch any of those, by doing this linking databases will not do any harm to the 3 servers right? And yes I think this might be the solution for my question if nothing serious happens just by linking those servers. Thank you! – MegaTrone May 10 '21 at 13:58
  • It can't harm the linked servers if you don't run harmful commands on them. To avoid the risk of doing so, you could link using an account that only has read permissions. – underscore_d May 10 '21 at 14:05
  • Thank you very much @underscore_d :) Highly appreciate your help! – MegaTrone May 10 '21 at 14:29

0 Answers0