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?