Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    Unanswered: Best way to compare two ENTIRE rows in seperate tables?

    Hi folks, I've got a fairly easy one here me thinx. I'm looking for the best way to compare two entire rows from two seperate tables which have the same primary key.

    Here's the basic lowdown:

    I get some data every night from an external system (cache') via DTS. This is more or less my "master" data which drives my application. I have just been informed of an interesting constraint. If any of the data changes in the external system, those changes do not become effective until the first day of the ensuing fiscal quarter.

    I'm solving this by running the DTS as normal, but populating a "duplicate" table which I will evaluate once per quarter for any changes. This is also the preferred solution because they would like to see a snapshot between current cache' data and the data my application is currently working with.

    So, I end up with two identically structured tables. both tables have the same primary key and can be linked by an id field with relative ease. What I would like to do is a full row comparision once this join is established.

    Right now I explicitly check the value of each column. ie:

    WHERE t1.field1 <> t2.field1 OR t1.field2 <> t2.field2 OR t1.field3 <> t2.field3 ... etc

    I'm hoping there is something buried in TSQL that I just don't know about that can handle comparing entire rows and tell me if they're different. Or perhaps there's another approach all-together.

    Any thoughts?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in my opinion, a JOIN with a column-by-column comparison is the best way to go

    WHERE t1.field1 <> t2.field1 OR t1.field2 <> t2.field2 OR t1.field3 <> t2.field3

    another approach:
    Code:
    select pkey
      from (
           select * from table1
           union
           select * from table2
           ) u
    group
        by pkey
    having count(*) = 2
    since UNION removes duplicate rows, this query will give you all the pkeys which have something different

    of course, then you gotta dive back into the tables to see the actual data, so ...
    Last edited by r937; 04-12-06 at 17:18.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Teddy
    I'm hoping there is something buried in TSQL that I just don't know about that can handle comparing entire rows and tell me if they're different. Or perhaps there's another approach all-together.
    There is.
    Code:
    select	a.pkey
    from	(select pkey, binary_checksum(*) as checkvalue from a) checksumsa
    	inner join (select pkey, binary_checksum(*) as checkvalue from b) as checksumb
    		on checksumsa.pkey = checksumsb.pkey
    where	checksumsa.checkvalue <> checksumsb.checkvalue
    BUT...binary_checksum values are not guaranteed to be different 100% of the time. There is a slight chance that the data could be modified in a way that leaves the checksum value unchanged, though the chances are slim.
    Have you considered placing a trigger on your secondary table to set a flag when a record is updated? In the end, that may be your best approach.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    When using binary checksums (which is the way I do this comparison across several databases and tables we have) you also have to be aware that there can be differences reported if one of the servers being used in the comparison is a LINKED server...
    http://www.dbforums.com/showthread.php?t=1213716
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Good stuff guys! I'll be leveraging a bit of what Rudy and Brett posted for what I need. I was positive there had to be a more elegant method than what I was doing...

    Thanks duders!
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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