This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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?
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.
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)