Results 1 to 7 of 7

Thread: binary checksum

  1. #1
    Join Date
    Sep 2003
    Posts
    176

    Unanswered: 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.

    Thanks

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Try 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)

  3. #3
    Join Date
    Sep 2003
    Posts
    176
    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.
    Last edited by vivek_vdc; 02-06-04 at 15:42.

  4. #4
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100
    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
    Last edited by TimS; 02-06-04 at 23:11.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100
    --
    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

  7. #7
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •