SQL Server 2005 - How to compare content of tables without using third party tools?
You may be aware about using Third party tools to compare the data between 2 tables. Using SQL Server 2005 you need not entirely dependant on such requirement, TABLEDIFF utility is the new addition to the SQL Server. This utility enables you to compare the table contents by giving consistent information such as whether data is same, are they different and also give what data in the table different.
From the command prompt where the SQL Server is installed you can run TABLEDIFF -? to get list of supported arguments. Bear in mind you cannot use the same utility when the SQL instance is not present. Books Online refers about data types mappings that are provided with the results:
| Source data type |
Destination data type |
|
tinyint |
smallint, int, or bigint |
|
smallint |
int or bigint |
|
int |
bigint |
|
timestamp |
varbinary |
|
varchar(max) |
text |
|
nvarchar(max) |
ntext |
|
varbinary(max) |
image |
|
text |
varchar(max) |
|
ntext |
nvarchar(max) |
|
image |
varbinary(max) |
The user must be a member of DB_OWNER fixed database role or CREATE TABLE permission. In this regard several different types of data comparison can be performed using this utility by specifying -q option that will quickly compare by providing the record count.
You can use this utility even to compare the data between Production and Development SQL instances.