Comparing tables with TABLEDIFF

Recently, I stumbled across a pretty cool utility called tablediff.  This utility is shipped standard with Microsoft SQL Server and is used to compare the data in two tables for non-convergence (dissimilarities).  The contingencies for this tool require that the tables being compared both have a primary key or a similar field.  This utility can be used from the command prompt or in a batch file which means it can be called from SSIS.  

The example in this post will compare two tables.  The first table is an existing table that we want to insert, update and delete records from/into.  The second table is a new table that will contain our inserts, updates and deletes.  To demonstrate, I will enter the below command into command prompt:

 tablediff -sourceserver LOCALSERVER -sourcedatabase TEMPDB -sourcetable NEW_TABLE -destinationserver  LOCALSERVER -destinationdatabase TEMPDB -destinationtable EXISTING_TABLE -et TABLEDIFFS -f D:\TABLEDIFF.SQL

 00

 This command specifies:

Below is what the result set looks like in command prompt

 01

 This is what the resultant table will look like in SQL. 

 02

 The tablediff executable is stored in the following location: C:\Program Files\Microsoft SQL Server\90\COM\.

 Here are some additional switches for tablediff:

-dt

Drop the result table specified by table_name, if the table already exists.

-et table_name

Specifies the name of the result table to create. If this table already exists, -DT must be used or the operation will fail.

-f [ file_name ]

Generates a Transact-SQL script to bring the table at the destination server into convergence with the table at the source server.

-o output_file_name

Is the full name and path of the output file.

 -q

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

-rc number_of_retries

Number of times that the utility retries a failed operation.

-ri retry_interval

Interval, in seconds, to wait between retries.

-strict

Source and destination schema are strictly compared.

-t connection_timeouts

Sets the connection timeout period, in seconds, for connections to the source server and destination server.

 http://technet.microsoft.com/en-us/library/ms162843.aspx

Advertisements

Tags: , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: