RSS
Feed
Wednesday, July 18, 2012 | 
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'
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
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
