nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to do an exact compare of 2 tables?

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

Transformation

Accepted answers

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.

All comments

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

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

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.

Quick Links

Popular Tags

This months top contributors

atcodedog05 19598

Qiu 15907

MarqueeCrew 13709

apathetichell 13703