Generating SQL Server synchronization scripts with Tablediff

Tablediff is a console that can compare data between tables in SQL Server. Also, because it's packaged in SQL Server, you don't need to buy additional tools if you want to check the lookup table in a different database environment.

In different database environments, DBAs often have to look for differences in their lookup tables (e.g., development, quality assurance, production, and so on). The data in these lookup tables must be adapted to all environments to ensure that the tests are accurate.

There are some very good tools on the market to make these comparisons and perform many other functions. But now you don't have to buy them, because SQL Server already has this tool, that's tablediff, it can do that for you.

Tablediff makes it easy for you to compare data in a table, and it can also create scripts for you to synchronize the program environment. In addition, as an excellent tool for synchronizing the lookup table between the test environment and the product environment, Tablediff is also useful for synchronizing data between the product server and the replication server, so that when replication problems occur, it can be an advantage.

Example

Tabeldiff is a console application, so you need to call it through a command prompt, batch file, or by using a xp_cmdshell SQL Server. In this example, I will build a small environment between the two databases on the same server, and then compare the data in the two tables. Here I invoke tablediff through a batch file.

The script in Listing a creates two databases and creates a SourceTable table in each database. The data is then inserted into the SourceTable table in each environment, where the data inserted into two tables is significantly different. (For my SQL Server 2005 environment, Tablediff is in this directory: C:Program filesmicrosoft SQL servercom.) The location of the file is important for you to specify the installation, because you need to know exactly where it is when you invoke the batch file to create it. )