[摘录]SQL server比较两张表的方法

 

引用作者 术之多 桦仔 , CSDN _BigMao

的相关文章 版权归原作者所有，如有侵权请联系删除

原文链接：

　　https://blog.csdn.net/weixin_46867655/article/details/106001982 (桦仔 2022-11-13)

　　https://www.shuzhiduo.com/A/pRdBjw11dn/ (_BigMao 2020-05-13)

tablediff 实用工具官方文档：

https://learn.microsoft.com/zh-cn/sql/tools/tablediff-utility?source=recommendations&view=sql-server-ver16

官方文档 How to Compare and Synchronize the Data of Two Databases：

https://learn.microsoft.com/zh-cn/sql/ssdt/how-to-compare-and-synchronize-the-data-of-two-databases?view=sql-server-ver15#ViewDifferences

参考：http://blog.itpub.net/26736162/viewspace-2721927/

 

使用软件功能进行比对的方法

一：结构比对；

       结构比对推荐用SqlCompare，比较直观

二：数据比对；

      数据比对推荐使用VS自动的比对功能，比较直观；使用步骤如下：

 

 

 

 

 

 

数据比对的一些方法

这里我们用两张表t1_old，t1_new来演示

方法介绍

方法一：老老实实看表结构和表记录数，弊端是根本看不到两张表的数据是否一致，只是看到表结构和记录数是否一致

--表结构：
 CREATE TABLE t1_old (
  id int NOT NULL,
  log_time DATETIME DEFAULT ''
) ;
 CREATE TABLE t1_new (
  id int NOT NULL,
  log_time DATETIME DEFAULT ''
) ;
 
--两表的记录数都为100条。
select count(*) from t1_old;
 
select count(*) from t1_new;

　　

方法二：加法去重 union 运算符排除重复的，但是有bug，在某些情形下不能简单表示结果集一致，相当于无效

由于Union 本身具备把上下两条连接的记录做唯一性排序，所以这样检测起来比较简单

SELECT  COUNT(*)
FROM    ( SELECT    *
          FROM      [t1_old]
          UNION
          SELECT    *
          FROM      [t1_new]
        ) AS T;
 
INSERT INTO [dbo].[t1_new]
        ( [id],[log_time] )
VALUES(1,''),(3,''),(4,'')
 
INSERT INTO [dbo].[t1_old]
        ( [id],[log_time] )
VALUES(1,''),(2,''),(3,'')
 
SELECT * FROM [dbo].[t1_new]
SELECT * FROM [dbo].[t1_old]
 
SELECT  COUNT(*)
FROM    ( SELECT    *
          FROM      [t1_new]
          UNION
          SELECT    *
          FROM      [t1_old]
        ) AS T;

两表数据

查询出来的结果是4

 

方法三：EXCEPT  减法归零

SELECT  COUNT(*)
FROM    ( SELECT    *
          FROM      [dbo].[t1_new]
          EXCEPT
          SELECT    *
          FROM      [dbo].[t1_old]
        ) AS T;
 
SELECT  COUNT(*)
FROM    ( SELECT    *
          FROM      [dbo].[t1_old]
          EXCEPT
          SELECT    *
          FROM      [dbo].[t1_new]
        ) AS T;
 
SELECT * FROM [dbo].[t1_new]
 
SELECT * FROM [dbo].[t1_old]

这里检测出来结果不对，那么就直接给出不一致的结论

 

方法四：用全表INNER JOIN，这个也是最烂的做法，当然这里指的是在表记录数超级多的情况下

DECLARE @t1_newcount BIGINT
DECLARE @count BIGINT
 
SELECT  @t1_newcount = COUNT(*)
FROM    t1_new;
 
SELECT  @count = COUNT(*)
FROM    [t1_old] AS a
        INNER JOIN [t1_new] AS b ON [b].[id] = [a].[id]
                                    AND [b].[log_time] = [a].[log_time] --如果表中还有其他字段的自行添加
PRINT @count
PRINT @t1_newcount
IF ( @count = @t1_newcount )
    BEGIN
        SELECT  'equal'
    END
ELSE
    BEGIN
        SELECT  'not equal'
 
    END 

 

方法五：借助SQLSERVER自带的tablediff工具，当初微软制作这个工具的目的就是用于比较复制中发布表和订阅表的数据一致

identical是相等的意思

 

方法六：借助发布端的验证订阅功能，验证订阅端跟发布端的数据是否一致

 

方法七：用checksum校验，比较两张表里的内容的checksum值是否一致

但是这种方法也只局限于两表结构一摸一样

我把[t1_new]表的数据复制到一张新的表以便进行比较

SELECT * FROM [dbo].[t1_new]
SELECT * FROM [dbo].[t1_newreplica]
 
SELECT SUM(CHECKSUM(*)) AS checksumvalue FROM [dbo].[t1_old]
SELECT SUM(CHECKSUM(*)) AS checksumvalue FROM [dbo].[t1_new]
SELECT SUM(CHECKSUM(*)) AS checksumvalue FROM [dbo].[t1_newreplica]

　

---

总结

从上面几种数据库提供的方法来看，用EXCEPT减法来归零相对来说比较可靠，其他的方法比较适合在特定的情形下来检测