Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2009
    Posts
    18

    Unanswered: Compare records in 2 tables

    In my regression test, I need to do a record-by-record comparision of 2 tables from 2 databases on 2 different servers (These 2 tables are actually the same table, just 2 different versions, old and new)

    I'm thinking of using a SQL script to do this in the following steps:


    (1) Connect to the servers and open up the 2 tables

    (2) Read and compare the 2 tables record-by-record. All the while, some how capture the records that are not in sync as I go along.


    I'm new with SQL script and really appreciate any tip you can give on how to go about doing this. Thank you for your help.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Try these, tell us what these don't tell you.
    In table 1, not in table 2
    Code:
    SELECT * FROM tab1 EXCEPT SELECT * FROM tab2
    In table 2, not in table 1
    Code:
    SELECT * FROM tab2 EXCEPT SELECT * FROM tab1
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just realised - these are on different servers. Do you know how (and are able) to link servers, and also about four part naming?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As Pootle Flump pointed out, you really need a linked server if you want SQL to compare these databases on different servers. A Primary Key or at least a Unique Index would also help a lot. If you can do these two things (Linked Server, and Primary Key), then a FULL OUTER JOIN can solve most of your problems.

    If you can't use the FULL OUTER JOIN for some reason, I'd probably export the two tables to text files, then compare those files. BCP will help you export your data, as will DTS or SSIS. There are a number of compare utilities, but I like UltraCompare (and all of the IDM tools) a lot.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Oct 2009
    Posts
    18
    Thank you for your responses, Pootle Flump and Pat Phelan.

    I think I can use your suggestions regarding the Linked Server.

    As for the actual record comparo: My main table is in PROD (Production) and the other one in QA (test). However, there is NOT necessarily a one-to-one record mapping between the 2 tables--there can be multiple QA records that corresponds to a given PROD record.

    My task is to match the QA records against their corresponding PROD records based on some given criteria (date, fee, etc) and look for discrepancies in other fields common between the 2 records.

    So my thinking has been along the line of using some kind of loop to loop thru my QA table and match against records in my PROD table. This seems simplistic but it is what I'm familiar with in my old-fashioned procedural world.

    Thanks for your help.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by knhcmp
    So my thinking has been along the line of using some kind of loop to loop thru my QA table and match against records in my PROD table. This seems simplistic but it is what I'm familiar with in my old-fashioned procedural world.
    Yeah - it was clear from your question that you are a procedural programmer. Dump the loop idea - you are working with relational databases now. Just as you don't use SQL to play around with the file system, you don't use loops in SQL (except in very specific circumstances).

    Now you have refined your question it has changed a little. You would need to be more specific about the schema (definition) of these tables and exactly what columns you are matching on and exactly what remaining columns you are looking for differences for.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Oct 2009
    Posts
    18
    Thank you Pootle Flump.

    It would be against my company policy for me to disclose details on the company's database, but I'll try my best to describe the issue at hand.

    So we try to compare records in two tables in separate database, P and Q, on 2 servers. The tables are actually 2 instances of the same table so they have identical fields.

    Currently, we extract the same range of records from the P and Q tables and dump them into a spreadsheet then look at the records side-by-side for discrepancies.

    Most of the time, they will be identical, but sometimes they are different because certain fileds contain different values. For example, the fee field is $3 in the P table but $4 in the Q table then we need to flag that record.

    To make thing a bit more interesting, the Q table may have duplicated records that should be ignored/filtered out. By duplicated I mean all their field values match with the corresponding P record except for the DateAdded field (A record can be added/re-added to the Q file multiple times).

    So in my test, I would be going down the spreadsheet looking for the fee discrepancy, flag the records that mismatch if the Q record is NOT a duplicated one.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    So you need to form an identical SELECT against each table and use EXCEPT clause (see pootle's post, but replace * with a field list).
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Oct 2009
    Posts
    18
    Sorry for my own misunderstanding, the comparo is not simply between 2 tables, but actually between 2 views--each is made up by fields from multiple tables.

    As such, I wouldn't think the EXCEPT operation can be used. Am I correct?

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    From the SQL perspective, there is little difference between a TABLE and a VIEW, especially for the way that you intend to use them.

    rdjabarov: Using the FULL OUTER JOIN, you could find the added/deleted/changed rows in a single pass, so I think it would perform much better than using EXCEPT. Am I missing something?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually, I would myself use a UNION with a COUNT(*) = 1 in a single operation but EXCEPT was quicker to type out. I don't really use FOJ any more. I was convinced by stuff like this.
    Better Alternatives to a FULL OUTER JOIN

    Also, Pat is correct that views and tables are the same thing in relational theory and pretty nearly so in SQL.

    All this should take minutes to test. Create your linked server and write out the EXCEPT SQL using the correct names and a column list and see what comes out. You are looking for problems without even trying the solution. The full outer join would be more or less the same solution so we can convert to that if you prefer once we know what works.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Oct 2009
    Posts
    18
    As a refresher: I'm pulling rows from 2 tables in 2 databases on 2 servers.

    Now I'm told that linked-server is not being used in our environment for security reason (why is this?).

    Without linked-server, what would be the next best alternative to extract records from these two separate databases for comparision purpose? Thanks!

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by knhcmp View Post
    (why is this?).
    You'll have to ask the person that made this rule. Essentially, if one server were compromised then any linked servers would also be compromised based on the permissions of the account used to link to them. If you believe your servers at risk of being compromised or you are in the habit of using accounts that have greater privileges than they should then it is a very sensible policy.

    Alternative to a linked server:
    OPENROWSET (Transact-SQL)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Oct 2009
    Posts
    18
    Thank you for all the help on this topic. You guys/gals in this forums are awsome.

    Being an old-world procedural guy, it's tough for me to wrap my head around how SQL can muster up the complex results it can so fast, so nonchalantly, so magically!

    I'm very new to SQL but already can sense how interesting and how powerful this SQL stuffs can be. I'll will try to report back on this topic some time later on. Thanks much!

Posting Permissions

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