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.
We plan to migrate data from a system to another system and do parallel run. Data from various sources is loaded into the existing system database DB1 and into the new system database DB2. From end-users' perspective, data from both databases should be same. There is no guarantee, for each table x, the record order in DB1 will be same as in DB2.
Given a list of table names, we like to do the following (pseudo code/workflow):
for each table x in a given list - retrieve data from the table x in DB1 => S1 - retrieve data from the table x in DB2 => S2 end for each compare the data (column by column) of S1 and S2 & report which column & values don't match
Procedure to retrieve data from table x
- retrieve data from table x - rename table x primary key column to "PRIME_ID" - transpose table x to key-value pairs with SCHEMA_TABLE, PRIMARY_COL, and PRIME_ID as key fields - output/append the transposed data to Alteryx database S
Challenge: - How can I dynamic rename the primary key header to PRIME_ID given a primary key header value for each table (see the sample below)?
Sample tablesLeft: Original tables; Right: Desired transformed tables
I suspect I have to use batch processing with macros to process each table. I am stuck in how to dynamic rename a header based on another dynamic input data (to determine which header to replace for each table. I hope someone can advise on the part I am stuck in.
If you have better idea on comparing tables from two databases, really appreciate if you can share with audience here.