Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

MSSQL: Checking for Equality of Tables (Hints only, please)

  1. Feb 12, 2016 #1

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Hi all,
    I am trying to find a method to determine if two tables T1, T2 (of any size) in MSSQL2012 are equal.
    Please just give hints

    My idea is to use the "Except" operator this way:

    Select * from T1
    Except
    (Select * from T2)

    And I should get an empty table with columns in both, but with no values entered.
    Does this work? Any other hints?
     
  2. jcsd
  3. Feb 12, 2016 #2

    fresh_42

    Staff: Mentor

    Only unspecific thoughts, since MSSQL isn't the version I used very often:

    Beside automatic PK do you have another one to specify single entries? I can't imagine this will work without 'where' clause plus 'not in'.
    Maybe not only PK differ but also timestamps or the spelling of char entries. (Runtime might get a problem, too.)
     
  4. Feb 12, 2016 #3

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Never mind, Fresh - Prinz_42 of Bel_ Deutschair , I think a correlated subquery using >< should do. Do you agree?
     
    Last edited: Feb 12, 2016
  5. Feb 13, 2016 #4
    Hints:
    1. Look up the documentation and you will see that that is exactly what EXCEPT is for.
    2. Do you think that you will always get the same results from
      Code (Text):
      SELECT * FROM t2 EXCEPT SELECT * FROM t1
    3. You don't need the ()s.
    4. Look at the documentation to see treatment of schema differences and edge cases.
    5. You could also look at the command line utility tablediff.
    6. Use capitals for reserved words.
    Edited: forget to hint about asymmetry
     
  6. Feb 13, 2016 #5

    fresh_42

    Staff: Mentor

    Depends on how you join them. If you're "lucky" you will select to each A.entry_m all B.entry_n with n≠m. What speaks against select A.entry where A.entry not in (select B.entry where A.entry = B.entry); beside runtime?
     
  7. Feb 13, 2016 #6

    Svein

    User Avatar
    Science Advisor

    I tend to do it the hard way. Insert a logical field in T2, set them all to "false" and use a query to set the field to "true" in all matching records. The ones left out are the records where the field value is still "false".
     
  8. Jun 6, 2016 #7

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Thanks, Anchovy, but does your answer assume the tables are normalized? I am thinking I have the same n distinct records in each, but these n records may be
    repeated a different number of times in each table, say record_1 appears 7 times in table 1, and appears 8 times in table 2 , etc. How do I account for this; should I select count for each record := record _i and then compare the counts for each?
     
  9. Jun 27, 2016 #8
    Sorry for the delay, I haven't been around for a while - but then there was even more of a lag for your question!

    It's not going to work with duplicate rows, but then you asked for hints not a complete solution! If there are (or could be) duplicates then yes you are going to need to come up with a different solution. Counting the occurrences for each distinct row in each table and compare the counts could work. Or sort both tables by the same complete ordering, add a sequence column and then use EXCEPT. Or write a script in your language of choice to iterate over sorted recordsets. Or use tablediff. It all depends on the context - why do you want to compare these tables, why might they be different and what do you want to do if they are?
     
  10. Jun 27, 2016 #9

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Thanks, it is a theoretical thing, just a challenge someone gave me to help me sharpen my SQL skills. I am allowed to get hints but not full answers.
     
  11. Jul 14, 2016 #10
    I think you can check UNION of both tables and find the rowcount which should equal the rowcount from table T1 as well as table T2. o_O
     
  12. Jul 14, 2016 #11

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Thanks all, from my reading I think it may be a good idea to use rank or dense rank .

    EDIT: I appreciate your suggestions and probably there are a few better than mine but this
    is the one I am most familiar with at this point.
     
    Last edited: Jul 20, 2016
  13. Jul 15, 2016 #12
    To first compare the structures of 2 tables you can use the system view information_schema.columns.:cool:
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook

Have something to add?
Draft saved Draft deleted



Similar Discussions: MSSQL: Checking for Equality of Tables (Hints only, please)
  1. Matlab and tables (Replies: 0)

Loading...