Could you please help me to understand if Optim can support the following migration scenario. Thanks a lot.
For example, I have a table Lane(lane_id, origin_id, dest_id), where lane_id is PK and it grows by DB sequence (i.e. surrogate key), and ori_id+dest_id is unique key on Lane (i.e. business key). And this lane table has a child table lane_detail (ld_id, lane_id, desc), where ld_id is pk, and lane_id is a foreign key to lane.
lane_id PK <------> lane_id FK
ori_id UK ld_id PK
dest_id UK desc
I have already archived the lane and lane_detail to a archive file, and plan to insert this archive file to a target DB (where lane and lane_detail already exist) with the following requirements.
When inserting the lane row to the target DB:
(1) First query the lane table in target DB by ori_id and dest_id, if a row is found, then use that row's lane_id when inserting the lane row in target DB, if no row is found then we just insert lane as usual.
(2) If a row is found in (1), then update the lane_id in child table lane_detail before inserting this table.
In simple, I want to migrate tables to target DB and during migration keep the surrogate keys in target DB if existed.
So I am looking at following 2 map column functions, which seems if I combine to use them in column mapping, can help for the above scenario.
1) Propagate function, this function assigns a value to a primary key or foreign key column and propagates that value to all related tables. It seems can help in (2) above
2) Lookup function, this function obtains the value for a destination column from a lookup table, according to the value in a source column.
So here is the expression that I attempted to try in column mapping editor for the source lane_id column.
I want you to help me understand if
1) The combination use of these 2 functions can help in my scenario, or do you have other suggestion?
2) By looking thru the LOOKUP function I didn't find any parameters that can help support "if no row is found in target lane, then we still use the original lane_id in source lane". Could you advise it?
Thanks for your help!