SQL Server Comparison Data differences

When doing data migration or replication, it is sometimes necessary to verify the data differences between the target table and the source table. Here are a few ways to verify data differences:

1. Two table connection queries, comparing the values of each column

Declare @vSQL varchar (max) Declare @vCols varchar (max) Create Table vTable1 (id int, StudentID int, Dept varchar), BookID int) Create Table vTable2 (id int, StudentID int, Dept varchar (ten), BookID int) Insert into vtable1select 1,123, ' CS ', 465 Unio n allselect 2,123, ' CS ', 345 Union allselect 3,223, ' TE ', 190Insert into Vtable2select 1,123, ' CS ', 465 Union allselect 2,223, ' Te ', 345 Union allselect 3,223, ' te ', 190--Get the column names from schema with case statements to Get 0 or 1 as result--N  OW, this would depend upon the columns of your actual tables--Data approachselect @vCols = Stuff ((Select ', case when a. ' + [name] + ' = B. ' + [name] + ' then cast (b. ' + [name]+ ' as varchar ') else cast (b. ' + [name] + ' as varchar (max)) + ' (o LD) ' + ' + Cast (A. ' + [name] + ' as varchar ') + ' (new) ' End as ' +[name] from sys.columnswhere object_id = objec t_id (' VTable1 ') for XML Path ("),")--concatenate the @vCols with main sqlset @vSQL = ' Select a.id, ' + @vCols + ' F Rom VTable1 ainner Join vTable2 b on b.id = a.ID ' Print @vSQLExec (@vSQL)--flag approachselect @vCols = Stuff ([Select ', case when a. ' +[name] + ' = B. ' + [name] + ' then 1 else 0 end as ' +[name] from Sys.columnswhere object_id= object_id (' VTable1 ') for XML Path (')), 1, 1, ') Set @vSQL = ' Select a.id, ' + @vCols + ' from VTable1 Ainner joins VTable2 b on b.id = a.ID ' Print @vSQLExec (@vSQL) Dr OP table Vtable1drop Table VTable2

　　

2. Compare total rows of two tables

Select count (1) from table_a

Select COUNT (1) from Table_b

If the table has a Time field, this way to compare is OK, this method can only see whether the total number of rows is consistent.

3. Tablediff tools provided by Microsoft

Compare tables with two data first

TRUNCATE TABLE functionmenu_t
INSERT INTO functionmenu_t
SELECT * FROM dbo. Functionmenu

Compare tables with different data

Update Top (1) functionmenu_t Set Id = ' AAAA '

　　

The comparison results show the difference between the two tables.

4. Verifying data consistency with replication tools

---------------------------------------------

Tablediff parameters

Tablediff [-?] | {-sourceserver Source_server_name[\instance_name]-sourcedatabase source_database-sourcetable SOURC  E_table_name [-sourceschema source_schema_name] [-sourcepassword source_password] [-sourceuser source_login ] [-sourcelocked]-destinationserver Destination_server_name[\instance_name]-destinationdatabase Subs Cription_database-destinationtable destination_table [-destinationschema destination_schema_name] [-des Tinationpassword Destination_password] [-destinationuser destination_login] [-destinationlocked] [-B Large_     Object_bytes] [-BF number_of_statements] [-c] [-DT] [-et table_name] [-f [file_name]]     [-O output_file_name]     [-Q]     [-RC Number_of_retries]     [-ri Retry_interval]    [-strict] [-t Connection_timeouts]}

-C Contrast Column
   -B Large data type the number of bytes to compare, any large data type column larger than this value will not be compared
-O the full path and name of the output file.
-Q only makes a comparison of the number of rows and schemas. 

SQL Server Comparison Data differences