For comparing data in tables you don't need any 3rd party tool, SQL Server ships with the tablediff utility which can be used to compare the data in two tables. You can use it for troubleshooting replication. This utility can be used from the command prompt or in a batch file to perform the following tasks:
Lets see how to use it...
For testing i've created two tables Table1 and Table2 in DatabaseA and DatabaseB respectively on my local Instance, Then inserted few records in each table.
create database DatabaseA
create table Table1(
tid int primary key,
insert into Table1 values(1,'Sarab')
insert into Table1 values(13,'Amit B')
insert into Table1 values(18,'Amit K')
insert into Table1 values(21,'Sachin')
--ServerB(I've created this on my local instance however, You can create this on another server\Instance)
create database DatabaseB
create table Table2(
tid int primary key,
insert into Table2 values(1,'Sarab')
insert into Table2 values(13,'Amit B')
insert into Table2 values(12,'Rishu')
insert into Table2 values(18,'MV Priyank')
Now if you query both the tables you can see that I've purposely inserted 2 same records in both the tables (tid 1 & tid 13 marked Blue in the screenshot) and inserted same primary key with different values for tname (tid 18 marked Red in the screenshot) to show you how TableDiff Utility consider these scenarios.
Location of the Utility File:The TableDiff.exe Utility can be found at this location: C:\Program Files\Microsoft SQL Server\100\COM
Note - In case you are using SQL Server 2005 replace 100 with 90.
To start the action launch command prompt and locate the directory having TableDiff.exe utility, then execute the command below:
TableDiff.exe -SourceServer . -SourceDatabase DatabaseA -SourceTable Table1 -DestinationServer . -DestinationDatabase DatabaseB -DestinationTable Table2 -et difft1 -f d:\my_Diff_File.sql
Most of the parameters are self explanatory, the odd ones I am explaining below:
-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. You can optionally specify a name and path for the generated Transact-SQL script file. If file_name is not specified, the Transact-SQL script file is generated in the directory where the utility runs.
To get the complete list of all supported parameters visit: http://msdn.microsoft.com/en-us/library/ms162843.aspx
When -et parameter is used, the result table will be created in Destination Database. This table returns all rows where the data got mismatched based on primary (or Unique) Key including the script to resolve the conflict. It also returns all those rows which are only present at source or destination.
Here's a screenshot of the output:
And -f parameter will generate a file with T-SQL Script named my_Diff_File.sql which will have all the commands to resolve all the conflicts. The Output looks like this:
-- Host: .
-- Database: [databaseB]
-- Table: [dbo].[table2]
DELETE FROM [dbo].[table2] WHERE [tid] = 12
UPDATE [dbo].[table2] SET [tname]=N'Amit K' WHERE [tid] = 18
INSERT INTO [dbo].[table2] ([tid],[tname]) VALUES (21,N'Sachin')
Points to consider:
Security Note: When possible, supply security credentials at runtime. If you must store credentials in a script file, you should secure the file to prevent unauthorized access.
Sarabpreet Singh AnandVice President - SQLServerGeeks.com
Sarabpreet is SQLServer MVP, DBA, Speaker, Trainer, Blogger and Community Lead. You can find him speaking at a local UG Event or a SQL Webcast. He has 8+ years of Experience and worked with Industry Leaders like Wipro, HP and HCL. He has many SQL Certifications under his belt. His core competency lies in administration of SQL Server. Always ready to help, online \offline. His life’s mantra is “Knowledge Increases by sharing so, Pass it on”. To know about his speaking engagements visit: here...
Follow Sarab on
I tried using the above statement with -et, but the table generated is not having the _offendingcolumns. I want to know which columns are having different values.
Can tablediff be used on temporary tables created using INTO clause. I tried using tablediff on temp tables created using INTO clause, but the output says -
"The replication table difference tool requires the comparison tables/views to have either a primary key, identity, rowguid or unique key column."
I do i set primary key, uniqye key, identity on temp tables created using SELECT * INTO ##PP..........
Leave a comment