Systems Engineering and RDBMS

TableDiff.exe utility in SQL Server 2005

Posted by decipherinfosys on December 27, 2007

We have talked about the command line utilities in SQL Server 2005 and have covered some of them like SQLCMD in some detail before. In this post, we will go over the tablediff.exe utility. This utility is typically used to troubleshoot data mis-matches in two tables in a replication scenario where the source table resides in an instance that acts as a Publisher and the destination table in an instance that acts as a subscriber. It can also be used to perform column level comparisons and to generate a T-SQL script to fix those discrepancies. It also has an option to do a fast comparison of the schema and the row counts in the tables. Below is a snapshot displaying all the different options of this utility and the location of this exe in the default installation of SQL Server 2005:

table_diff_1.jpg

Let’s follow this up with a simple example to illustrate the capabilities of this utility. It takes in two sets of input:

1) Connection Information for the Source and the Destination locations.

2) Comparison Options: a) Comparison of Schemas: Regular or Strict, b) Comparison using Rowcounts or Column Comparisons, and c) T-SQL script generation to sync. up the destination table data with the source.

Let us create a table with the same structure in two different databases – for our demo sake, we are creating these on the same instance:

USE MAINDB
GO
CREATE TABLE TABLEDIFF_TEST (COL1 INT NOT NULL PRIMARY KEY, COL2 VARCHAR(10) NULL)
GO
INSERT INTO TABLEDIFF_TEST (COL1, COL2) VALUES (1, ‘ABCD’);

And then in another database, we will create the same table but with different data set:

USE MISYS
GO
CREATE TABLE TABLEDIFF_TEST (COL1 INT NOT NULL PRIMARY KEY, COL2 VARCHAR(10) NULL)
GO
INSERT INTO TABLEDIFF_TEST (COL1) VALUES (1);
INSERT INTO TABLEDIFF_TEST (COL1) VALUES (2);
INSERT INTO TABLEDIFF_TEST (COL1) VALUES (3);

And now, we can run the tablediff.exe utility from the command line to generate the differences and output it to an external file (we can also choose to write to a table):

table_diff_2.jpg

And here is the output of that command:

Table [MISYS].[dbo].[TABLEDIFF_TEST] on (local) and Table [MAINDB].[dbo].[TABLEDIFF_TEST] on (local) have 3 differences.
Err COL1 Col
Mismatch 1 COL2
Src. Only 2
Src. Only 3
The requested operation took 0.21875 seconds.

It is denoting that where the value of COL1=1, there was a mis-match in the data value of COL2 and the records with values of COL1=2 and COL1=3 were present only in the source and not the destination. Let us generate the DML statements through this utility to get the data in sync. in the destination database (this can be done by using the “-f” option):

Table [MISYS].[dbo].[TABLEDIFF_TEST] on (local) and Table [MAINDB].[dbo].[TABLEDIFF_TEST] on (local) have 3 differences.
Fix SQL written to DIFFIX.633343501980863750.sql.
Err COL1 Col
Mismatch 1 COL2
Src. Only 2
Src. Only 3
The requested operation took 0.21875 seconds.

This time when we ran the command, it created a separate SQL file for us which has these DML statements (the name of the file is indicated above and it’s location is the same location as that of the tablediff.exe utility. Here are the contents of that file:

– Host: (local)
— Database: [MAINDB]
— Table: [dbo].[TABLEDIFF_TEST]
UPDATE [dbo].[TABLEDIFF_TEST] SET [COL2]=NULL WHERE [COL1] = 1
INSERT INTO [dbo].[TABLEDIFF_TEST] ([COL1],[COL2]) VALUES (2,NULL)
INSERT INTO [dbo].[TABLEDIFF_TEST] ([COL1],[COL2]) VALUES (3,NULL)

This utility by itself would be pretty cumbersome to use but you can combine this along with a simple script file to loop through the list of the tables and then do a difference report between source and destination and also generate all the DML statements to get the data in sync.. As stated before as well, this is typically used for replication but can also be used for data and schema comparisons and sync. up.

We covered it so that you are aware that something like this exists natively in SQL Server and can be used for troubleshooting issues. Though if you are really looking for an easy way to address those issues, you should look at Red-Gate’s tools (SQL Compare and SQL Data-Compare).

About these ads

Sorry, the comment form is closed at this time.

 
Follow

Get every new post delivered to your Inbox.

Join 77 other followers

%d bloggers like this: