TableDiff utility to compare data between two databases

Introduction

As a database professional, we might come across in situation where we need to compare data row by row or column wise between two tables which either resides in same database or in same instance or may be in different instance in different server. 

What do you in this situation?

1.) Do you write down script of your own?

2.) Do you use any third party software?

3.) Do you use “TableDiff” utility comes with SQL Server itself?

Third option, out of all of the above, seems good to me as we neither need to invent the zero again by writing down the script by our own nor we need to pay extra money to compare data.

“TableDiff” is one of the wonderful and oldest utility provided by Microsoft. It works fine with SQL Server 2000 to the latest SQL Server edition. However, I am providing you the script and example from my SQL Server 2008 instance.

Getting Ready

Before you move forward, you need to find out two tables whose data you wanted to compare. It might be in publisher/subscriber in replication, it might be in two different databases you are using for scale out or may be anywhere else.

If you don’t have this situation at the moment in your environment, don’t worry, I will be giving a script to raise the scenario to test “TableDiff” utility.

How to do it...

1.)    Open New Query window in you SQL Server

2.)    Create two different database by using following script:

USE master

GO

CREATE DATABASE TableDiffDb1

GO

CREATE DATABASE TableDiffDb2

GO

3.)    Create a sample table in “TableDiffDB1” database with following script

 USE TableDiffDb1

GO

--if orders table is already there. you can delete it than create new one with name "Orders"

IF OBJECT_ID('orders', 'U') IS NOT NULL BEGIN

      DROP TABLE orders

END

GO

--creating table

CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)

GO

--inserting 1000 sample rows into table

INSERT INTO orders (OrderDate, Amount, Refno)

SELECT TOP 1000

      DATEADD(minute, ABS(a.object_id % 50000 ), CAST('2010-02-01' AS DATETIME)),

      ABS(a.object_id % 10),

      CAST(ABS(a.object_id) AS VARCHAR)

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

GO

4.)    Creating “Orders” table in second database by copying 900 records (out of total 1000 records) from “Orders” table from “TableDiffDB1” database by using following script.

USE TableDiffDb2

GO

--if orders table is already there. you can delete it than create new one with name "Orders"

IF OBJECT_ID('orders', 'U') IS NOT NULL BEGIN

      DROP TABLE orders

END

GO

--creating table

CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)

GO

--inserting 900 sample rows into table from TableDiffDb1 database's Orders table

INSERT INTO orders (OrderDate, Amount, Refno)

SELECT TOP 900 OrderDate,Amount,Refno FROM TableDiffDb1.dbo.orders

5.)    Now use following command to see the difference between two tables.

exec master..xp_cmdshell 'tablediff -sourceserver [RITESH-SHAH\MSSQL2008] -sourcedatabase TableDiffDb1 -sourcetable Orders -destinationserver [RITESH-SHAH\MSSQL2008] -destinationdatabase TableDiffDb2 -destinationtable Orders -et Difference -f D:\OrdersDifference.sql'

Replace your server instance name in “SourceServer” and “destinationServer” parameter in above given command and you will get one .SQL file in D drive. Running that SQL file will insert all missing records in “Orders” table of “TableDiffDb2” database as it shows you the list of all missing records there.

There's more...

I would like to draw your attention to some of the facts which can help you if you don’t find “TableDiff” working in your environment.

Remember that “TableDiff.exe”  file resides in installation directory of SQL Server by default which is “C:\Program Files\Microsoft SQL Server\100\COM” in my case.  So, there is chance that “TableDiff” command is not accessible via DOS prompt, you have to set path for “TableDiff” in “ServerVariable”. 

You can reach “ServerVariable” by “MY Computer Properties > Advanced System Settings > Advanced > Environment Variables > System Variables > PATH”

If you find any path under “PATH” in “ServerVariable”, you can put “;” (semicolon) after that path and can add your path for “TableDiff”.

Generally people tend to use “TableDiff” from DOS prompt itself or via .bat (batch file) file but I have used “xp_cmdshell” extended stored procedure to show the use of command right from SQL Server but there may be a chance that “xp_cmdshell” is disable in your environment. If your security constraint allows, you can enable “xp_cmdshell”. For more details about the steps, click here.

Reference: Ritesh Shah

http://www.sqlhub.com
 

Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”