What's the [table]diff? Diff'ing SQL Server tables with the Microsoft provided tablediff command line utility

simple talk - SQL Tools - SQL Server tablediff utility

Rob Sheldon continues on his quest to explain all those command-line tools such as SQLCMD, Logparser, SQLIO and tablediff that are part of SQL Server. TableDiff is great for comparing tables, as when you run automated tests that check a result against a table of expected values. The best way to learn TableDiff is to see it in action and Rob talks you through several examples.

Among the many command-line utilities provided with SQL Server, one of particular note is tablediff, a handy tool that lets you compare the data in two tables in order to identify any discrepancies. Though originally intended primarily as a utility for troubleshooting non-convergence in a replication topology, tablediff can actually be used to perform a row-by-row comparison of any two tables that share a similar schema so is ideal for running automated unit tests for database routines. In addition, you can use the tool to generate the T-SQL script necessary to update the data in the second table to conform to the first table, if discrepancies are found.

Getting Started with the tablediff Utility

The best way to learn how to use the tablediff utility is to see it in action. This article includes a number of examples that demonstrate how to compare tables that contain both matching and non-matching data. The tables in our examples reside in databases on different SQL Server instances, one SQL Server 2012, the other SQL Server 2008 R2, both installed on the same local system. However, you can compare any two tables—within the same database or within different databases on the same or different servers—so if you want to try out these examples on your system, modify the T-SQL code and example tablediff commands as necessary to accommodate your setup.

Now let’s get started. First we’ll compare identical tables on the two SQL Server instances. To prepare the environment, I ran the following code on each instance to create the TableDiffDB database and the DiffTest1 table within the database:

...

The tablediff utility compares the data in the source table to the table in the destination table. In the example above (as well as those to follow), the localhost\sqlsrv2012 instance serves as the source and the localhost\sqlsrv2008R2 instance serves as the destination. Be aware, however, that when using the tablediff utility to compare two tables, the source table must be configured with a primary key or an IDENTITY or ROWGUID column.

NOTE: The tablediff utility uses Windows authentication to connect to SQL Server. However, you can also specify a username and password when connecting to the source instance, the destination instance, or both. For information about the login-related options (as well as details about other tablediff options), see the Books Online topic “tablediff Utility.”

...

Making the Most of the tablediff Utility

If you haven’t already discovered the tablediff utility, you’ll find it well worth your time to check it out. The tool is part of the SQL Server installation and is relatively simple to use. Just open a command prompt window and start typing. Not only can you compare tables across SQL Server instances, but you can also generate the T-SQL scripts necessary to update those tables. And you can run the utility from within scripts created in command shells such as PowerShell, thus letting you automate operations and reuse code. The tablediff utility might not meet all your needs when comparing tables, but in those cases where it does, you’ll likely find tablediff to be a valuable addition to your arsenal of tools.

tablediff Utility

The tablediff utility is used to compare the data in two tables for non-convergence, and is particularly useful for troubleshooting non-convergence in a replication topology. This utility can be used from the command prompt or in a batch file to perform the following tasks:

• 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.

• Perform a fast comparison by only comparing row counts and schema.

• Perform column-level comparisons.

• Generate a Transact-SQL script to fix discrepancies at the destination server to bring the source and destination tables into convergence.

• Log results to an output file or into a table in the destination database.

sigh... another day, another thing I didn't know. Why is it the older I get, the less I know? (What Yoda? You think, "Wisdom, on the road, you are...?" Byte me Yoda. You short little garden gnome looking...Oh damn. Did I say that out loud? ;)

Anyway...

There's a number of times where this free utility could have come in real handy. Now I just have to remember about it the next time I need something like it... :P