Comparing tables

Last Post 10 May 2006 05:41 PM by mwesch. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
natasha
New Member
New Member

--
10 May 2006 12:41 PM
Is there a slicker way of comparing the contents of two identical tables without using a third party tool.
Currently I have a code together but I am looking for a better way to achieve this.
Here is the code:
select t.id, t.lname, t1.Id, t1.Fname
from (select * from test1
join test2 on test1.id = test2.id
where test1.lname <> test2.lname) t
full outer join
(select * from test1
join test2 on test1.id = test2.id
where test1.Fname <> test2.Fname) t1
on t.id = t1.id


and so on........
when you have 100 + fields in the table this becomes a very cumbersome solution.

Thanks,
Nat
nosepicker
New Member
New Member

--
10 May 2006 05:26 PM
You can try using CHECKSUM or BINARY_CHECKSUM:

SELECT A.*, B.*
FROM test1 A JOIN test2 B ON test1.id = test2.id
WHERE CHECKSUM(A.lname, A.fname, ...) <> CHECKSUM(B.lname, B.fname, ...)

Using BINARY_CHECKSUM will give different results. Checkout the "CHECKSUM" section in Books Online for more details.
mwesch
New Member
New Member

--
10 May 2006 05:41 PM
You might look at CHECKSUM(*) function. Here's an example.

-------------------------------------------------------------
create table #t1 (pkey int, val1 int, val2 varchar(25), val3 varchar(25))
create table #t2 (pkey int, val1 int, val2 varchar(25), val3 varchar(25))

insert #t1 (pkey, val1, val2, val3) values (1, 10, 'value1', 'description1')
insert #t1 (pkey, val1, val2, val3) values (2, 25, 'value2', 'description2')
insert #t1 (pkey, val1, val2, val3) values (3, 15, 'value3', 'description3')
insert #t1 (pkey, val1, val2, val3) values (4, 40, 'value4', 'description4')
insert #t1 (pkey, val1, val2, val3) values (5, 20, 'value5', 'description5')
insert #t1 (pkey, val1, val2, val3) values (6, 10, 'value6', 'description6')
insert #t1 (pkey, val1, val2, val3) values (7, 10, 'value6', 'description6')
insert #t1 (pkey, val1, val2, val3) values (8, 10, 'value6', 'description6')

insert #t2 (pkey, val1, val2, val3) values (1, 10, 'value1', 'description1')
insert #t2 (pkey, val1, val2, val3) values (2, 250, 'value2', 'description2')
insert #t2 (pkey, val1, val2, val3) values (3, 15, 'value3', 'description3')
insert #t2 (pkey, val1, val2, val3) values (4, 40, 'value4', 'description4')
insert #t2 (pkey, val1, val2, val3) values (5, 20, 'value5', 'typo5')
insert #t2 (pkey, val1, val2, val3) values (6, 10, 'value6', 'description6')
insert #t2 (pkey, val1, val2, val3) values (9, 10, 'value6', 'description6')

select coalesce(t1.pkey, t2.pkey) as Pkey,
case
when t1.pkey is null then 'T1 MISSING'
when t2.pkey is null then 'T2 MISSING'
when t1.Chk <> t2.Chk then 'DIFFERENT'
else 'IDENTICAL'
end
as Comparison
from (select pkey, checksum(*) as Chk from #t1) as t1
full outer join (select pkey, checksum(*) as Chk from #t2) as t2
on t1.pkey = t2.pkey

drop table #t1
drop table #t2
natasha
New Member
New Member

--
10 May 2006 07:59 PM
Thank you to both of you. Honestly I was not aware of function "CHECKSUM".
Upon futher reading about it, using this function will tell me if values are same or different but it will not actually give me the values of the field. If any fields are resulted as different then to debug the difference in values I will have to traverse/query each records. Going through this sort of comparision against 100 K+ records and 100+ fields, I might lose my sanity.
Thanks again for the help. I am going to try to use the function and cross the bridge when needed.
Nat
cmdr_jpskywalker
New Member
New Member

--
11 May 2006 04:43 AM
Use the CheckSum/SELECT INTO to find the records that are different. Use dynamic SQL/Cursor to query each field. You can use the INFORMATION_SCHEMA to retrieve the COLUMN names.
You are not authorized to post a reply.

Acceptable Use Policy