A couple of years ago I found a little gem of a utility called tablediff ,a handy tool that lets you compare the data in two tables in order to identify discrepancies.
Where to find it?
You can locate it by using.
How to use it? Syntax is very simple and self-explanatory.
Let’s prepare the environment, to do so run the following script to create two tables.
Notice that both tables were created under the same instance, but you can compare from two different instances/servers.
Now let’s create a simple compare, it would look something like this:
Notice that I used my integrated security login, but you can specify a user name and password.
If we now run the tablediff command, our results would look something like this:
Generating T-SQL Scripts
One of the most interesting features of the tablediff utility is the ability to generate T-SQL script to bring the table at the destination into convergence with the table at the source.
So in order to do so, let’s run the following tablediff command.
-f [ file_name ] You can optionally specify a name and path for the generated Transact-SQL script file. If file_name is not specified, the Transact-SQL script file is generated in the directory where the utility runs.
Voila now we have a .sql file with the INSERTS and UPDATES statements to update the target.
Get the most out of it
I have been working a lot with SQL Express instances, so I built a small program in .NET that runs every night, executes tablediff command in PowerShell and takes care of the replication that is not available in SQL Express (You can setup Subscriber only). I going to share more about this in the near future.
Note: I am aware that we can achieve the same result by using the new SQL Server Data Tools (SSDT), but I think that TableDiff is a very handy tool.
Enjoy!!!
