Compare Two Tables Using T-SQL or Command Line Utility (tablediff.exe)

There is out too many ways to compare two tables in SQL server but I prefer to do this task in an easy way using EXCEPT and INTERSECT introduced before SQL Server 2005 but more enhanced and improved performance since SQL Server 2005.

Where do we make use of this comparison?

For example if you have same database in multiple environments and you would like to verify the data in those tables are also same or not.  Mostly this will be useful to compare the production tables to development server tables

Using EXCEPT and INTERSECT (T-SQL)

Conditions to use EXCEPT and INTERSECT

The number and the order of the columns must be the same in all queries or tables you are comparing.
EXCEPT and INTERSECT Commands performs based on results of the queries.
The data types must be compatible.
I will explain how to use this commands in different cases as below,

Case 1: Same number of columns and structure of the tables.

Comparing tables with same structure in different databases or instances to find matched and unmatched data in those tables

/*Returns all rows in Table_A that do not match exactly in Table_B*/

SELECT * FROM TABLE_A EXCEPT SELECT * FROM TABLE _B
/*Returns all rows in table B that do not match exactly in table B*/

SELECT * FROM TABLE_B EXCEPT SELECT * FROM TABLE_A

/* Returns all matched rows in both table_A and table_B*/

SELECT * FROM TABLE_A INTERSECT SELECT * FROM TABLE_B

Case 2: Unequal number of columns in two tables.

For example Table A has 5 columns and Table B has 10 Columns which 5 out of 10 columns are equal. Even though tables have unequal columns still you can use EXCEPT and INTERSECT to compare those 5 columns data

EX: SELECT COL1, COL2, COL3, COL4, COL5 FROM TABLE_A EXCEPT SELECT COL1, COL2, COL3, COL4, COL5 FROM TABLE_B

Case 3: Comparing tables in two different remote servers.

In this case you can use linked server as shown below

EX: Select * from Table_A EXCEPT select * from openquery(LinkedServer_Name, ‘Select * from Table_B’)

MSDN Reference: EXCEPT and INTERSECT

Using Command Line Utility (tablediff.exe):

The tablediff utility is used to compare the data in two tables. One luckier thing is it ships with SQL server for other replication purposes but we can make use of it for our purpose. It not only finds the unmatched data in two tables but also generates the script to synchronize those tables in comparison. This utility can be used from the command prompt or in a batch file to perform the following tasks:

A row by row comparison between a source table in an instance of Microsoft SQL Server acting as a replication Publisher and the destination table at one or more instances of SQL Server acting as replication Subscribers.
Perform a fast comparison by only comparing row counts and schema.
Perform column-level comparisons.
Generate a Transact-SQL script to synchronize unmatched rows on target table.
Log results to an output file or into a table in the destination database.
This tool can be found under following path in SQL server 2008 and 2011

C:\Program Files\Microsoft SQL Server\100\COM  ‘tablediff.exe’ Or if you cannot find the exact path to the location Open CMD> type dir /s tablediff.exe> then it displays the path to thetablediff.exe application.

Syntax: Open CMD and Use below syntax to run tablediff.exe utility

“C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe” -sourceserver MyServer1

-sourcedatabase MyDatabase1

-sourcetable MyTable1

-destinationserver MyServer1

-destinationdatabase MyDatabase1

-destinationtable MyTable2

-et DiffsTable