{"id":1732,"date":"2013-12-02T00:00:00","date_gmt":"2013-12-02T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/sql-server-tablediff-utility\/"},"modified":"2021-05-11T15:57:22","modified_gmt":"2021-05-11T15:57:22","slug":"sql-server-tablediff-utility","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/tools-sql-server\/sql-server-tablediff-utility\/","title":{"rendered":"SQL Server tablediff utility"},"content":{"rendered":"
\n

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. Although it is not fast in doing so, it can  be used for running automated unit tests for database routines. In addition, you can, with limitations, 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.<\/p>\n

Getting Started with the tablediff Utility<\/h1>\n

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.<\/p>\n

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<\/code> database and the DiffTest1<\/code> table within the database:<\/p>\n

USE master;\nGO\n\nIF DB_ID('TableDiffDB') IS NOT NULL\nDROP DATABASE TableDiffDB;\nGO\n\nCREATE DATABASE TableDiffDB;\nGO\n\nUSE TableDiffDB;\nGO\n\nIF OBJECT_ID('DiffTest1', 'U') IS NOT NULL\nDROP TABLE DiffTest1;\nGO\n\nCREATE TABLE DiffTest1\n(\n  DiffID INT IDENTITY(101, 1) PRIMARY KEY,\n  DiffName VARCHAR(25) NOT NULL\n);\nGO\n\nINSERT INTO DiffTest1 (DiffName)\nVALUES ('test tablediff utility');\nGO 10\n\n<\/pre>\n
Notice that the  DiffTest1<\/code>  table contains the DiffID<\/code>  and DiffName<\/code>  columns. I populated each row in the tables with the same DiffName<\/code> value, but let the  IDENTITY<\/code>  property populate the DiffID<\/code> values, starting with 101<\/code>.<\/p>\n
That’s all you need to test the tablediff utility;  however, before you do, you might want to first prepare your Windows environment. Although tablediff is installed by  default, you cannot reference the utility without using the full path name or switching to the folder where the  executable file is stored. However, you can modify your operating system’s  Path<\/code>  environmental variable to include the full path to that folder. Once you add the path, you can run the tool at a command  prompt without having to navigate to the utility’s folder. <\/p>\n
The process you use to modify the  Path<\/code>  system variable varies from one Windows operating system to the next. In Windows 7, for example, one way to access the Path<\/code>  variable is to open Control Panel, click System<\/code>  and<\/code> Security<\/code>,  and then click System<\/code>.  In the left pane, select Advanced<\/code> system<\/code> settings, which launches the  System<\/code>  Properties<\/code>  dialog box. On the Advanced<\/code>  tab, click the Environment<\/code> Variables<\/code>  button. When the Environmental<\/code> Variables<\/code> dialog box appears, select the Path<\/code>  variable in the System<\/code>  variables list, and then click Edit<\/code>. This launches the  Edit<\/code> System<\/code> Variable<\/code>  dialog box. In the Variable<\/code> value<\/code>  text box, append the current value by adding a semi-colon and the path to the tablediff directory, as in  ;C:\\Program Files\\Microsoft SQL Server\\110\\com<\/code>.<\/p>\n
Once you’ve modified the  Path<\/code>  variable, you’re ready to go. Open a command prompt window and enter the following command:<\/p>\n
tablediff -sourceserver localhost\\sqlsrv2012 -sourcedatabase tablediffdb -sourcetable difftest1 -destinationserver localhost\\sqlsrv2008r2 -destinationdatabase tablediffdb -destinationtable DiffTest1<\/pre>\n
Before we go into the command’s details, first note  that a tablediff command is a single-line operation. It wraps across multiple lines here because of margin limitations;  however, you should not press  Enter<\/b> until you’ve typed in the entire command.  <\/p>\n
Now let’s look at the command itself. After  specifying the  tablediff<\/b> command name, you must include the six options  contained in the example:<\/p>\n