Overblog Follow this blog
Edit post Administration Create my blog
Balavardhan Reddy Narani

Compare data between two tables in SQL Server 2005

August 6 2009 , Written by Balavardhan Reddy Published on #SQL Server2005

Inorder to find the difference of two tables in schema and data betweeen Source and distination tables.  we can use Tablediff in SQL Server 2005

You can run this utility from the command line or a batch file.

Ex: if you have two Databases one for Production and another for Development in differerent servers, and this two databases have a table called Employee.  If you want to find the differences between Production and Delelopment Databases employee table. Do the below procedure.

Syntax to run the tablediff untility

C:\Program Files\Microsoft SQL Server\90\COM>tablediff -sourceserver "<SourceServerName>" -sourcedatabase "<SourceDBName>" -sourcetable "<SourceTableName>" -destinationserver "<DestServerName>" -destinationdatabase "<DestDBName>" -destinationtable "<DestTableName>"

It can give the Output result as Below

Microsoft (R) SQL Server Replication Diff Tool
Copyright (C) 1988-2005 Microsoft Corporation. All rights reserved.

User-specified agent parameter values:
-sourceserver <SourceServerName>
-sourcedatabase <SourceDBName>
-sourcetable <SourceTableName>
-destinationserver <DestServerName>
-destinationdatabase "<DestDBName>
-destinationtable <DestTableName>

Table [SourceDBName].[dbo].[SourceTableName] on <SourceServerName> and Table [DestDBName].[db
o].[DestTableName] on <DestServerName> have 24 differences.

Err                     <Table Primary Key>
Mismatch                523       --If difference the record
Src. Only               745       --If the record existed only in Source

You can also save this information inot File by using -f parameter with the file name

C:\Program Files\Microsoft SQL Server\90\COM>tablediff -sourceserver "<SourceServerName>" -sourcedatabase "<SourceDBName>" -sourcetable "<SourceTableName>" -destinationserver "<DestServerName>" -destinationdatabase "<DestDBName>" -destinationtable "<DestTableName>" -f: "c:\difference"

It can create the SQL Script file in C:\ with difference.sql as script file.


Share this post

Repost 0

Comment on this post