• Replies 5 replies
  • Subscribers 337 subscribers
  • Views 19 views
  • Users 0 members are here
Related

Any way to do a differential on two versions of a table?

Levi Sauerbrei
$organization

I'm trying to figure out why the numbers in one of our long standing reports shifted unexpectedly last Friday.  So far, nothing  we can find points us to the culprit.

Our test system is holding the copy of the database from the day before the problem popped up so in theory I should be able to compare some of the tables from the test system to their counterparts in the live system and come up with some likely suspects.

So the question is this.  Is there a straightforward way to do a differential on two tables that are structurally the same? (other than writing a bunch of SQL from scratch)  I'd like to see both changes in existing records and any rows that were added.  I can get the additional rows pretty easily through the primary keys, but seeing what changes may have happened to existing records might be trickier.

Parents Reply Children
  • Former Member
    Former Member $organization in reply to Levi Sauerbrei

    There’s a commercial tool from RedGate that says it does this – I haven’t used it. Might be others.

     

    http://www.red-gate.com/products/SQL_Data_Compare/index.htm

     

     

    Ken McSwain Business solutions Manager

    kmcswain@sydneyoperahouse.com

    T+61 2 9250 7876  F+61 2 9251 7821  M 0418 659 360

     

    SYDNEY OPERA HOUSE BENNELONG POINT

    GPO BOX 4274, SYDNEY NSW 2001, AUSTRALIA

    SYDNEYOPERAHOUSE.COM

     

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Levi Sauerbrei
    Sent: Tuesday, 26 January 2010 09:47
    To: Ken McSwain
    Subject: Re: [Tessitura Technical Forum] Any way to do a differential on two versions of a table?

     

    To answer my own question a bit.  It looks like the EXCEPT and INTERSECT commands in SQL will do alot of this.  I'd still be interested in something that is a little more visual. Something like WinDiff, but for the database.

    From: Levi Sauerbrei <bounce-levisauerbrei8271@tessituranetwork.com>
    Sent: 1/25/2010 2:31:07 PM

    I'm trying to figure out why the numbers in one of our long standing reports shifted unexpectedly last Friday.  So far, nothing  we can find points us to the culprit.

    Our test system is holding the copy of the database from the day before the problem popped up so in theory I should be able to compare some of the tables from the test system to their counterparts in the live system and come up with some likely suspects.

    So the question is this.  Is there a straightforward way to do a differential on two tables that are structurally the same? (other than writing a bunch of SQL from scratch)  I'd like to see both changes in existing records and any rows that were added.  I can get the additional rows pretty easily through the primary keys, but seeing what changes may have happened to existing records might be trickier.




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

    Please consider the environment before printing this email.
=====This message is intended for the addressee(s) named and may contain confidential information.
If you are not the intended recipient, please delete it and notify the sender.
Views expressed in this email are those of the individual sender and are not necessarily the views of the Sydney Opera House Trust=====
  • Former Member
    Former Member $organization in reply to Former Member
    Redgate is awesome you can get the trial version for your purposes. Then purchase it later if allowable by your business. We used the trial version here, when we get money in the budget we will be getting it!
    Naomi

    From: Tessitura Technical Forum <forums-technical@tessituranetwork.com>
    To: Naomi Williams
    Sent: Mon Jan 25 18:42:30 2010
    Subject: RE: [Tessitura Technical Forum] Any way to do a differential on two versions of a table?

    There’s a commercial tool from RedGate that says it does this – I haven’t used it. Might be others.

     

    http://www.red-gate.com/products/SQL_Data_Compare/index.htm

     

     

    Ken McSwain Business solutions Manager

    kmcswain@sydneyoperahouse.com

    T+61 2 9250 7876  F+61 2 9251 7821  M 0418 659 360

     

    SYDNEY OPERA HOUSE BENNELONG POINT

    GPO BOX 4274, SYDNEY NSW 2001, AUSTRALIA

    SYDNEYOPERAHOUSE.COM

     

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Levi Sauerbrei
    Sent: Tuesday, 26 January 2010 09:47
    To: Ken McSwain
    Subject: Re: [Tessitura Technical Forum] Any way to do a differential on two versions of a table?

     

    To answer my own question a bit.  It looks like the EXCEPT and INTERSECT commands in SQL will do alot of this.  I'd still be interested in something that is a little more visual. Something like WinDiff, but for the database.

    From: Levi Sauerbrei <bounce-levisauerbrei8271@tessituranetwork.com>
    Sent: 1/25/2010 2:31:07 PM

    I'm trying to figure out why the numbers in one of our long standing reports shifted unexpectedly last Friday.  So far, nothing  we can find points us to the culprit.

    Our test system is holding the copy of the database from the day before the problem popped up so in theory I should be able to compare some of the tables from the test system to their counterparts in the live system and come up with some likely suspects.

    So the question is this.  Is there a straightforward way to do a differential on two tables that are structurally the same? (other than writing a bunch of SQL from scratch)  I'd like to see both changes in existing records and any rows that were added.  I can get the additional rows pretty easily through the primary keys, but seeing what changes may have happened to existing records might be trickier.




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

    Please consider the environment before printing this email.
=====This message is intended for the addressee(s) named and may contain confidential information.
If you are not the intended recipient, please delete it and notify the sender.
Views expressed in this email are those of the individual sender and are not necessarily the views of the Sydney Opera House Trust=====



    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
  • $organization in reply to Levi Sauerbrei

    There's also this timely post from a SQLServerCentral daily that I received this morning:

    Comparing data between tables seems like a simple thing to do, but it can eat up a lot of a SQL Server professional's time. Stephen Tirone shows how multiple assignment variables can be used to make this task a little easier. More »

     

  • $organization in reply to Chris Wallingford

    Thank you all for the information!  I hacked through my problem in a more mundane fashion, but its always cool to learn new stuff.  Both Redgate and the multiple assignment variables look intriguing.  I'll keep both in mind when future problems arise.