/fdata%2F2707821%2Favatar-blog-1086523565-tmpphpHmlYOB.gif){kind=avatar}
Compare data between two tables in SQL Server 2005
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
To be informed of the latest articles, subscribe:
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
Comment on this post
