How to do an exact compare of 2 tables?

[debbieliske]

I have 2 database tables that I need to compare and get an output of the differences. The tables contain the same columns. One table is newer than the other and I need to see what records have been changed or added or removed. How would I do this and get an output of the changed data with an indicator as to which table it came from?

Thanks

[Bob_Blackey]

Hi Debbie,

It depends a little on your data but in general use a JOIN tool.

If the newer table is the L input and the older table the R and you Join on ALL the fields then in the outputs:

J - gives you the records which haven't changed

L - gives you new records or records which have changed

R - gives you records which have either been deleted or changed.

If you join on just the key columns (usually not including numeric data) then it's easier to see which columns were deleted or changed.

Hope that helps, if you can post some sample data we could fine tune what you need to JOIN on.

Cheers,

Bob

[gnans19]

Have you heard of CrewMacros by @AdamR_AYX. There is a macro 'Expect Equal' under "Crew Test" group.

For more information: http://www.chaosreignswithin.com/2016/06/crew-macro-pack-2016-q2-release.html

[suli · Accepted solution]

Hi @debbieliske,

I think I would try to transpose the data, so you will have 2 tables with columns like : Key, Field Name, Field Value, Source Table and Date (if necessary). Now you can 'Join Multiple' by Key and Field Name, and with one formula compare Field Values from tables A and B.

With this structure you can track which data have been added, deleted (field value missing from one table) or changed. And this model is easily adaptable for changing number of columns and do not need to write multiple formulas for multiple fields.

Hope this helps. Let me know if you have any questions.

[debbieliske]

This is perfect, thanks! I 

[hamdylie]

If I am not wrong, the "Expect Equal" tool expects the data on both sides have been sorted or in the same order. If the order of the data and, I suspect, the columns in both tables are not in a same order, then the tool will generate an error (I am using CREW 2016 version)

[dhtay]

@suli Thanks for the great solution!