Tablediff utility is one handy tool that I use in my day to day life as DBA to compare the data of one table against another table to identify if there are any differences between the tables. We can also generate the TSQL Script necessary to update the data in the second table to sync it with the other table.
The source table in the comparison must contain at least one primary key, identity, or ROWGUID column.
Now lets directly go into action and let me show you all how it works. For this I have created two tables with similar data in a Test database.
Below is the command: (type the command in command prompt in single line)
tablediff -sourceserver localhost -sourcedatabase Test -sourceschema Person -sourcetable ContactType -destinationserver localhost -destinationdatabase Test -destinationschema Person -destinationtable TestContactType
The command takes a few basic parameters to run
Here you can see that result says that both tables are identical. Now lets insert and update few data in both the tables so that we can sync them.
— Delete from Second Table
DELETE from [Person].[TestContactType]
— Update single record in Second Table
set Name=‘Junior DBA’
— Insert Record in the First Table
INSERT INTO [Person].[ContactType] (Name,ModifiedDate) values (‘Plumber’,GETDATE())
Now lets again run the command in tablediff to find out the data difference:
We can see it clearly highlights the 3 differences in these two tables. But the coolest feature of tablediff utility is the ability to generate TSQL scripts including the DML statements to update the destination table to sync it with the source table. To generate the script we need to add -f option.
Below script is generated :
Hope now you will be able to use this utility in a better way.