Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How to Compare the Data in Two Tables Without Any 3rd-Party Tool?

By Sarabpreet Singh Anand,

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:

  • A row by row comparison between a source table in an instance of Microsoft SQL Server acting as a replication Publisher and the destination table at one or more instances of SQL Server acting as replication Subscribers.
  • Perform a fast comparison by only comparing row counts and schema.
  • Perform column-level comparisons.
  • Generate a Transact-SQL script to fix discrepancies at the destination server to bring the source and destination tables into convergence.
  • Log results to an output file or into a table in the destination database.

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.

--On ServerA
create database DatabaseA
go
use DatabaseA
go
create table Table1(
tid int primary key,
tname varchar(20))
go
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
go
use DatabaseB
go
create table Table2(
tid int primary key,
tname varchar(20))
go
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')

The source table in the comparison must contain at least one primary key, identity, or ROWGUID column.

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

 

Parameters used:

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:

  • The tablediff utility cannot be used with non-SQL Server servers.
  • Tables with sql_variant data type columns are not supported.

Permissions Required:

  • To compare tables, you need SELECT ALL permissions on the table objects being compared.
  • To use the -et option, you must be a member of the db_owner fixed database role, or at least have CREATE TABLE permission in the subscription database and ALTER permission on the destination owner schema at the destination server.
  • To use the -dt option, you must be a member of the db_owner fixed database role, or at least have ALTER permission on the destination owner schema at the destination server.
  • To use the -o or -f options, you must have write permissions to the specified file directory location.

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.

Have Fun!

Sarabpreet Singh Anand
Vice President - SQLServerGeeks.com

Total article views: 4702 | Views in the last 30 days: 5
 
Related Articles
FORUM

Data Flow Destinations - Insert Only?

If you create any Data Flow Destination, is it ONLY for inserting?

FORUM

SSIS : Insert/update source to destination table

SSIS :Insert/update source to destination table with 2 primary keys

FORUM

SSIS problem with oledb destination and too many insert columns

SSIS problem with oledb destination and too many insert columns and sp_cursor

FORUM

Retrieve data from Data Flow Destination after task executes

An ID is created in the destination table on insert, this ID is needed elsewhere in the Control Flow...

FORUM

Creating Header AND Footer to a flat file destination file

Creating Header AND Footer to a flat file destination file

Tags
tablediff utility    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones