Original: How to quickly compare two tables in SQL Server
How to quickly compare two tables in SQL Server
Unconsciously write the last blog post for 2014 years ~
In general, how to detect the content of the two tables is consistent, reflected in the replication at the end of the publisher and the end of the subscriber on the data above
I've got a list of ways to solve this kind of problem from the database level.
The first step of course is to check whether the number of records is consistent, otherwise do not think of other methods ~
Here we use two tables t1_old,t1_new to demonstrate
Method Introduction
Method One: Honestly look at the table structure and the number of table records, the disadvantage is that the two tables do not see the data is consistent, just see the table structure and record number is consistent
--table structure: CREATE TABLET1_old (IDint not NULL, Log_timeDATETIME DEFAULT "') ;
CREATE TABLEt1_new (IDint not NULL, Log_timeDATETIME DEFAULT "') ;--the number of records for both tables is 100. Select Count(*) fromT1_old;Select Count(*) fromT1_new;
Method Two: The addition de-duplication Union operator excludes duplicates, but there are bugs, in some cases it is not simple to represent the result set consistent, equivalent to invalid
Since the Union itself has a unique sort of record of the upper and lower connections, it is relatively simple to detect.
SELECT COUNT(*) from(SELECT * from [T1_old] UNION SELECT * from [t1_new] ) asT;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] ) asT
Two-table data
The result of the query is 4.
Method Three: EXCEPT subtraction zeroing
SELECT COUNT(*) from(SELECT * from [dbo].[t1_new] EXCEPT SELECT * from [dbo].[T1_old] ) asT;SELECT COUNT(*) from(SELECT * from [dbo].[T1_old] EXCEPT SELECT * from [dbo].[t1_new] ) asT;SELECT * from [dbo].[t1_new]SELECT * from [dbo].[T1_old]
If the results are not correct, then the inconsistent conclusions are given directly.
Method Four: Use the full table inner JOIN, this is also the worst practice, of course, this refers to the table records in the case of super-many cases
DECLARE @t1_newcount BIGINTDECLARE @count BIGINTSELECT @t1_newcount = COUNT(*) fromt1_new;SELECT @count = COUNT(*) from [T1_old] asaINNER JOIN [t1_new] asB on [b].[ID] = [a].[ID] and [b].[Log_time] = [a].[Log_time] --If there are other fields in the table that you add yourselfPRINT @countPRINT @t1_newcountIF(@count = @t1_newcount ) BEGIN SELECT 'Equal' END ELSE BEGIN SELECT 'Not equal' END
Method five: With SQL Server's own tablediff tool, Microsoft made this tool to compare data in the published and subscribed tables in replication
Identical are equal meanings.
Method Six: Verify that the Subscriber is consistent with the data on the publishing side with the verification subscription function at the publisher
Method Seven: Compare the checksum values of the contents of two tables with checksum check
But this approach is only confined to the two-table structure.
I copy the data from the [t1_new] table to a new table for comparison
SELECT * from [dbo].[t1_new]SELECT * from [dbo].[T1_newreplica]SELECT SUM(CHECKSUM (*)) asChecksumvalue from [dbo].[T1_old]SELECT SUM(CHECKSUM (*)) asChecksumvalue from [dbo].[t1_new]SELECT SUM(CHECKSUM (*)) asChecksumvalue from [dbo].[T1_newreplica]
Summarize
From the methods provided in the above several databases, it is relatively reliable to use except subtraction to zero, and other methods are more suitable for detection in specific situations.
If there is a wrong place, welcome everyone to shoot brick O (∩_∩) o
How to quickly compare two tables in SQL Server