来自两个不同服务器的两个表的比较

Question

我试图在两个不同的服务器中找到两个表之间的差异。我应该如何成功地完成这项任务?

这是我目前拥有的:

SELECT 
'TABLE1-ONLY' AS SRC, T1.*       
FROM
(      
:connect SPXPBDB      
SELECT *       
FROM FINLS.FIN.DBO.EMPID        
EXCEPT     
:Connect SPXFINDB     
SELECT *     
FROM FIN.DBO.EMPIDFIN     
) AS T1      
UNION ALL      
SELECT 
'TABLE2-ONLY' AS SRC, T2.*     
FROM
(     
:Connect SPXFINDB       
SELECT *      
FROM FIN.DBO.EMPIDFIN     
EXCEPT      
:connect SPXPBDB      
SELECT *
FROM FINLS.FIN.DBO.EMPID       
) AS T2;       

Answer 1

我会使用SRC字段在2选择之外做一些事情，例如group by对它们或使用where语句:

SELECT 
A,B,C, SUM(COUNTER) AS COUNTER
FROM
(
SELECT 
'TABLE1-ONLY' AS SRC, T1.*, 1 AS COUNTER    
FROM
(      
***  
) AS T1      
UNION ALL      
SELECT 
'TABLE2-ONLY' AS SRC, T2.*, 1 AS COUNTER
FROM
(     
****       
) AS T2
)
GROUP BY A,B,C <-- Fields from T1 

现在您可以查看记录是否在两个表/数据库中