0

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

Drumbeg
  • 1,914
  • 1
  • 15
  • 22
user501307
  • 59
  • 1
  • 2
  • 9

1 Answers1

0

Was able to fix by recording the id'd in bridging temporary table and updating the id later in the loop.

user501307
  • 59
  • 1
  • 2
  • 9