Re: Tools for moving normalized data around

On 1/18/23 13:15, Gavan Schneider wrote:
> On 19 Jan 2023, at 6:47, Peter wrote:
>
>> Now I want to grab some part of the data, on a certain condition
>> (let's say all records belonging to user 'Bob', if there is a "user"
>> table somewhere at the tree-bottom), and move it to another database
>> with the very same layout - which is already populated with data
>> and runs a different instance of the same application.
>>
>> Grabbing the intended records is just some dead-simple Selects. But
>> then inserting them into the other database is not fun, because some
>> primary keys will likely collide.
>>
> A very small sliver in this problem:
> The key collision problem could be avoided if the unique and arbitrary keys were UUID
> Many of the other keys should be related to their respective “table of truth” so the migration is dependant on these being the same across the locations
>
> The rest TBA down thread :)
>
> Regards
>
> Gavan Schneider

You'll have to "know" the tree and order inserts accordingly. Starting
with the independent tables, then their dependants, then /their/
dependants etc.
And if the ids are currently integers, any chance you can negate the in
transit?  You'll have to disable any sequence that's in play.