DBAs often have to find the differences between lookup tables in different database environments (i.e., development, quality-assurance, staging, and production). The data in these lookup tables are required to be the same across environments to ensure that your testing is accurate.
There are a variety of great tools on the market that will run these comparisons, as well as perform many other functions. You don't need to go out and buy one because SQL Server comes with a tool called TableDiff that will do this for you.
TableDiff allows you to easily compare the data in tables, and it creates scripts for you to synchronize your environments. In addition to being a great tool for synchronizing lookup data between your testing and production environments, TableDiff is also very useful for synchronizing data between production servers and replication servers to accommodate for when replication problems occur.
TableDiff is a console application, so you will need to invoke it by command prompt, batch file, or via SQL Server using xp_cmdshell. In this example, I will set up a small environment between two databases on the same server and compare the data between two tables. I will invoke the TableDiff utility via a batch file.
The script in Listing A creates two databases and a SourceTable in each database. Data will then be added to the SourceTable table in each environment, with the table having slightly different data in each environment. (For my SQL Server 2005 environment, TableDiff is located here: C:\Program Files\Microsoft SQL Server\90\COM. The location of the file is important for your specific installation because you will need to know the location for the creation of your batch file.)
In this example, I will invoke the TableDiff utility with the necessary parameters to compare the data in the SourceTables between DatabaseA and DatabaseB. The script in Listing B passes the SourceServer, SourceDatabase, SourceTable, DestinationServer, DestinationDatabase, and DestinationTable as parameters to the TableDiff utility. The server and table name will be the same for each sitation because I am comparing a table with the same name on the same database server inside two different databases. The last option I pass is the location where the synchronization scripts will be placed. I am storing the scripts on the C:/ drive in a file named diffs.txt.
Once I execute the batch file, the text in Listing C is placed inside of the C:\diffs.txt file. This TSQL script can be run to synchronize the table between the two databases.
Ease of use
The TableDiff utility is easy to use and configure, and because it is packaged with SQL Server, it does not require any additional purchases to synchronize tables between environments. Because of its ease of use and availability, you can develop a variety of solutions to automatically build scripts to synchronize your environments as necessary.
Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at email@example.com.
Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.