SQL Server TableDiff.exe a hidden gem.

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!!!

An Accidental DBA has been born.

Accidental DBA

So how did I end up a SQL Server DBA/Developer?

Well, it is a boring story that I

will try to make short. I started out by getting a Bachelor’s degree in Engineering Systems from the Universidad Quezalcoatl en Irapuato, in Mexico (Yes, I am Mexican and that's why I am going to write in Spanish too) then I learned that working with code was probably my best strength.

I worked for a while for some companies in Mexico and I was very happy with all the stuff that I was doing, but through a good friend I got a job as a Software Developer in Dallas, TX, working with VB6, .NET, SQL Server and other stuff. As I worked with each of the technologies I found that I had an aptitude for SQL Server (I want to confess that I really hated SQL for a couple of years) and here I am 8 years later working for the same company, looking for ways to share the things I’ve learned and finding that the more I know, the more I need to learn.

So how did you become involved with SQL Server?