I ran into an unpleasant situation when it came to replacing one data table with another. But I want to keep this question short.
You have two Data Tables:
* data_table_01 :: This has no linked columns (completely independent).
* data_table_02 :: This has linked columns to data_table_1
Let’s say that data_table_01 is a reference table that has become stale. (Mine isn’t a reference table – it’s more complicated, but the spirit is the same).
Now let’s say I want to replace data_table_01 with, say, data_table_01b. Some of the columns will be deleted; some added; some columns will have .upper() run on them, other .lower(), etc. You get the idea.
In the end, every row in this refreshed, transformed data_table_01b table will still be business-related to data_table_02, but you could never know it from its new column contents or names. Those have all been changed. You can only depend before & after Row_Ids from data_table_01 to data_table_01b
How to prepare for and execute this sort of migration? Thank you.
Presumably you are writing a function to loop over the rows in data_table_01b?
What we did is introduce a uuid column and used that as a temporary link between tables that way you don’t need to know the row id that you don’t yet have… if that makes sense?
Once your data is in table 1 you can create the links again by using the uuid as the argument for the search/get function
To be honest, I sort of got caught off guard by this, so I did things manually (and quickly).
Yes, it kind of makes sense. Let me see if I can echo that back: You generated a UUID column – not necessarily seeded by any information in either table, but just random UUIDs – then applied them to, in my example, corresponding rows in both data_table_01 and data_table_01b?
Is that correct? Maybe you can elaborate and thanks for sharing.
Not helpful now, after the fact, but this sort of thing could also be done in place in the original table as a multi-step process (this assumes you don’t need the original table left around for reference).
Add new columns
Update data as needed (running upper, etc)
Delete unneeded columns
Your links from the other table will still be intact, and the table will be in the state you want.
