dcsimg
Results 1 to 1 of 1

Thread: MS SQL / Oracle - Comparing data in fields in two tables

  1. #1

    Thread Starter
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Java (JSP) Hell!
    Posts
    5,304

    MS SQL / Oracle - Comparing data in fields in two tables

    Just a problem / thought I had.

    A request I had was to compare two table that have some of the same fields (and perhaps more or less than each other).
    One is of data before processing, the other is after some processing has been applied.

    So the question:
    Is there an easy way to compare the fields, show the ones that have different data given that each of the records in the two tables has the same priimary key ?

    Ways I thought of:
    1. manually create an Sql statement each time for the two tables in question
    2. create the sql dynamically via Access/Excel/VBA/favourite programming lang
    (if you get a list of the fields and can see which is the primary key and which fields are in both tables)
    3. extract each of the fields names and data from source table a using the pk, then add in the second tables data if matches pk and fieldname. run a comparison, any that match are excluded. This leaves a list of mismatches, using pk and which field had the problem.
    Probably need to flag that actually found matching records and not that some are missing in each table...
    4. possibly use the minus join (but this sometimes didnt work right for some reason)

    I was thinking it may be possible in a Stored Proc using TSql/PlSql.
    But I thought I'd ask if anyone has had experience of t his and if there is a simpler way to compare?
    Last edited by Ecniv; Jul 26th, 2018 at 02:55 PM.

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width