November 9, 2015 at 4:31 am
Hi All,
Checking for dynamic tsql script or stored proc which takes 2 tables (it can be any table in the database) as input, do a row comparison and output me the differences with the row number and row mismatch value between to rows.
I have researched a little bit came across tablediff.exe tool. It tells which row but it doesnt tell me what are the exact differences between the column values for the mismatched rows.
Does anybody has a tsql script to share for row comparing and find out the column mistmatch's.
My research:
use master
go
create database src
go
create database dest
go
use src
go
create table emp
(c1 int not null primary key,
c2 varchar(100)
)
go
insert into emp
select 101,'row-1'
union all
select 102,'row-2'
union all
select 103,'row-3'
union all
select 104,'row-4'
union all
select 105,'row-5'
go
select * from src..emp
go
use dest
go
create table emp
(c1 int not null primary key,
c2 varchar(100)
)
go
insert into emp
select 101,'row-1'
union all
select 102,'row-2'
union all
select 103,'row-3'
go
-- updating a row at src
update src..emp
set c2 = 'row111'
where c1 = 101
-- updating a row at trg
update dest..emp
set c2 = 'row333'
where c1 = 103
select * from src..emp
go
/*
c1 c2
101 row111
102 row-2
103 row-3
104 row-4
105 row-5
*/
select * from dest..emp
go
/*
c1 c2
101 row-1
102 row-2
103 row333
*/
C:\Program Files\Microsoft SQL Server\120\COM>tablediff.exe -sourceserver . -sourcedatabase src -sourcetable emp -destinationserver . -destin
ationdatabase dest -destinationtable emp
Microsoft (R) SQL Server Replication Diff Tool
Copyright (c) 2014 Microsoft Corporation
User-specified agent parameter values:
-sourceserver .
-sourcedatabase src
-sourcetable emp
-destinationserver .
-destinationdatabase dest
-destinationtable emp
Table [src].[dbo].[emp] on . and Table [dest].[dbo].[emp] on . have 4 differences.
Err c1
Mismatch 101
Mismatch 103
Src. Only 104
Src. Only 105
thanks in adavnce
November 9, 2015 at 4:55 am
Although it doesn't actually list the differences, if you use the -f switch in Tablediff, it will produce the SQL to fix the differences for you.
November 9, 2015 at 5:58 am
Thanks Ian. Indeed very helpful switch -f.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply