binary checksum

Hi,
Can anyone provide me with the syntax for comparing rows of two tables using binary checksum? The tables A and B have 8 & 9 columns respectively. The PK in both cases is Col1 & Col2. I want checksum on Columns 1 to 8.
ThanksTry this draft:

drop table test
drop table test2
go
create table test(id int, col1 int,col2 varchar(5),col3 datetime)
create table test2(id int, col1 int,col2 varchar(5),col3 datetime)
go
insert test values(1,1,'a','02/03/2004')
insert test values(2,2,'b','02/04/2004')
insert test values(3,3,'c','02/05/2004')
insert test values(4,4,'d','02/06/2004')
insert test2 values(1,1,'a','02/03/2004')
insert test2 values(2,2,'b','02/04/2004')
insert test2 values(3,3,'f','02/05/2004')
insert test2 values(4,4,'d','02/01/2004')
go
select t.*
from test t
join test2 t2 on t2.id=t.id
where CHECKSUM(t.col2,t.col3)<>CHECKSUM(t2.col2,t2.col3)|||Hi,

The code doesn't work for this case.

drop table test
drop table test2
go
create table test(id int, col1 int,col2 varchar(5),col3 datetime)
create table test2(id int, col1 int,col2 varchar(5),col3 datetime)

insert test values(4,4,'d','02/06/2004')
insert test values(4,4,'e','02/06/2004')

insert test2 values(4,4,'d','02/06/2004')
insert test2 values(4,4,'e','02/06/2004')

select *
from test

select *
from test2

select t.*
from test t
join test2 t2 on t2.id=t.id
where CHECKSUM(t.col2,t.col3)<>CHECKSUM(t2.col2,t2.col3)

--

All my data is like this. The rows are the same but still checksum selects the rows. Please help.|||drop table test
drop table test2
go
create table test(id int, col1 int,col2 varchar(5),col3 datetime)
create table test2(id int, col1 int,col2 varchar(5),col3 datetime)

insert test values(4,4,'d','02/06/2004')
insert test values(4,4,'e','02/06/2004')

insert test2 values(4,4,'d','02/06/2004')
insert test2 values(4,4,'e','02/06/2004')

select *
from test

select *
from test2

select t.*
from test t
join test2 t2 on t2.col1=t.col1 AND t2.col2=t.col2 -- Join on PK
where BINARY_CHECKSUM(t.id,t.col3) <> BINARY_CHECKSUM(t2.id,t2.col3)

-- A much more complex, but its the way I do it.

SELECT t.*
from test t
JOIN
(
SELECT t1.col1, t1.col2, BINARY_CHECKSUM(*) as bin_ck_sum
from test t1
) AS X1 ON t.col1 = X1.col1 AND t.col2 = X1.col2
JOIN
(
SELECT t2.col1, t2.col2, BINARY_CHECKSUM(*) as bin_ck_sum
from test2 t2
) AS X2 ON t.col1 = X2.col1 AND t.col2 = X2.col2
WHERE X1.bin_ck_sum <> X2.bin_ck_sum|||TimS, your method is the most appropriate when comparing one entire record to another, but vivek_vdc only wants to check on 8 of the 9 columns in one table against the 8 columns in the other table. Binary_Checksum(*) on both tables will not do this comparison.|||--
TimS, your method is the most appropriate when comparing one entire record to another, but vivek_vdc only wants to check on 8 of the 9 columns in one table against the 8 columns in the other table. Binary_Checksum(*) on both tables will not do this comparison.
--

I agree but, I gave him two different solutions.

The Second, is how I would compare tables and it can be adapted to his problem; I have found that the second solution works best for me. Thier is no reason he can't replace the star with what columns he wish to compare.

THE PROBLEM was no one was joining on the PK of the tables.

Tim S|||You have to join on the primary keys change the join to use the columns in your primary key!

select t.*
from test t
-- Join on PK1 & PK2 ( the Primary Key COLUMNS )
join test2 t2 on t2.PK1=t.PK1 AND t2.PK2=t.PK2
where BINARY_CHECKSUM(t.id,t.col3) <> BINARY_CHECKSUM(t2.id,t2.col3)

If you want any more help please response to the newsgoup. Please give your create table and insert data that MATCHES your tables.

Tim S

--Original Message--
From: vivek_vdc
Sent: Monday, February 09, 2004 3:46 PM
To:
Subject: binary checksum post help

TimS - The code that you have given is assuming col1 & col2 are the PK but I have id & Col1 as PK. The code doesn't work in this case. Please advise on how I should proceed?