How to compare two tables in SQL server

SQL Server is having a command line tool (TableDiff) to compare the data in two tables.It will perform the following task.

1. 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.
2. Perform a fast comparison by only comparing row counts and schema.
3. Perform column-level comparisons.
4. Generate a Transact-SQL script to fix discrepancies at the destination server to bring the source and destination tables into convergence.
5. Log results to an output file or into a table in the destination database.

eg:

"C:\Program Files\Microsoft SQL Server\90\COM\TableDiff.exe" -sourceserver "SERVERNAME" -sourcedatabase "DBNAME" -sourceschema "dbo" -sourcetable "SOURCETABLE1" -sourceuser "sa" -sourcepassword "PASSWORD" -destinationserver "SERVERNAME" -destinationdatabase "DBNAME" -destinationschema "dbo" -destinationtable "SOURCETABLE2" -destinationuser "sa" -destinationpassword "PASSWORD" -dt -o "C:\Documents and Settings\renjuraj\My Documents\diff.xls"

Run the above command in command prompt.Please refer your sql server installation path(C:\Program Files\Microsoft SQL Server\90\COM\TableDiff.exe).

-Yuva