I'm kind of reverse engineering the my DB for business purposes, I'll keep this short and simple.I have two tables(a_visit,b_visit) and bridging table(a_to_b_visit) with foreign key id's from both. I need to duplicate insert into A_visit,b_visit based on the budget_id with new budget id and also handle the bridging table relationships.How do I handle the id's or insert records into bridging tables as well dynamically.For example I'll be inserting new records into using pl/sql into table A_visit,b_visit.
insert into a_visit
select new_seq_id,<new budget_id>,<other columns> from a_visit where budget_id=10
insert into b_visit
select new_seq_id,<new budget_id>,<other columns> from b_visit where budget_id=10
insert into a_b_visit
select new_seq_id,a_visit_id,b_visit_id from a_to_b_visit
where a_visit_id= old_a_id=<> and b_visit_id=old_b_id
(I'll be getting budget_id dynamically from other procedure).
TABLE A_VISIT
id, budget_id
1, 10
2, 10
TABLE B_VISIT
id, budget_id
4, 10
5, 10
A_TO_B_VISIT
id,a_id,b_id(unq(a_id,b_id))
1, 1,4
2, 2,4
I'm trying to solve above through pl/sql , I'm stuck on mapping newly generated one with the existing in bridging. Please advice!!.HopeI did not confuse much