Cascade INSERT using SEQUENCE - ORACLE

Question

I need to copy records from two tables and re-insert them once again into these tables with only one field different (PK for parent table, PK and FK for child table). PK can be taken from SEQUENCE, the problem I am facing is how to cascade update the FK in the child table.

I have the following table My_List, where Sup_ID is Primary Key

My_List

+--------+----------+-----------+
| Sup_ID | Sup_Name |  Sup_Code |
+--------+----------+-----------+
|      1 | AA       |        23 |
|      2 | BB       |        87 |
|      3 | CC       |        90 |
+--------+----------+-----------+

And the following table My_List_details, where Buy_ID is Primary Key (taken from SEQUENCE, assumed already created) and Sup_ID is Foreign Key points at My_List.Sup_ID

My_List_details

+--------+--------+------------+------------+------------+
| Buy_ID | Sup_ID | Sup_Detail | Max_Amount | Min_Amount |
+--------+--------+------------+------------+------------+
|     33 |      1 | AAA        |          1 |         10 |
|     34 |      2 | BBB        |         11 |         20 |
|     35 |      3 | CCC        |         21 |         30 |
|     36 |      2 | BBB        |         11 |         20 |
+--------+--------+------------+------------+------------+

---------------------------------------------------

Objectives

Write PL/SQL Function does the following:

1. Copy My_List records and re-insert then with the new PK -> Sup_ID values taken from SEQUENCE.
2. Copy My_List_details records and re-insert them with the new Sup_ID foreign key according My_List.Sup_ID, and Buy_ID from SEQUENCE.

------------------------------------------------------------------------------

Expected Outcome

My_List

+--------+----------+----------+
| Sup_ID | Sup_Name | Sup_Code |
+--------+----------+----------+
|      1 | AA       |       23 |
|      2 | BB       |       87 |
|      3 | CC       |       90 |
|      4 | AA       |       23 |
|      5 | BB       |       87 |
|      6 | CC       |       90 |
+--------+----------+----------+

My_List_details

+--------+--------+------------+------------+------------+
| Buy_ID | Sup_ID | Sub_Detail | Max_Amount | Min_Amount |
+--------+--------+------------+------------+------------+
|     33 |      1 | AAA        |          1 |         10 |
|     34 |      2 | BBB        |         11 |         20 |
|     35 |      3 | CCC        |         21 |         30 |
|     36 |      2 | BBB        |         11 |         20 |
|     37 |      1 | AAA        |          1 |         10 |
|     38 |      2 | BBB        |         11 |         20 |
|     39 |      3 | CCC        |         21 |         30 |
|     40 |      2 | BBB        |         11 |         20 |
+--------+--------+------------+------------+------------+

What I have started with is the following:

BEGIN
    FOR   d  IN (
                    SELECT  Sup_Name, Sup_Code
                    FROM    My_List
                )
    LOOP
        INSERT INTO MY_List SELECT Sup_ID.NEXTVAL, Sup_Name, Sup_Code 
    END LOOP;
END;
/

However, I am not sure about the following:

• How to replace FK in My_List_Details with the new values of PK in My_List. My_List_Detail
• Extend the function to update any number of child tables (Cascade update), (e.g. FK for five tables point to PK of My_List, need to be updated in the same manner).

Answer 1

I found the following solves my problem (given that sup_seq and buy_seq are two created SEQUENCES):

DECLARE
   CURSOR cOld IS
      SELECT *
      FROM my_list;

   new_id  NUMBER;
BEGIN
   FOR rOld IN cOld LOOP
      new_id := sup_seq.NEXTVAL;  -- Get the ID to use for the new row

      INSERT INTO my_list
             (sup_id,sup_name,sup_code)
      VALUES (new_id,rOld.sup_name,rOld.sup_code);

      INSERT INTO my_list_details
            (buy_id,sup_id,sup_detail,max_amount,min_amount)
      SELECT buy_seq.NEXTVAL,new_id,sup_detail,max_amount,min_amount
      FROM   my_list_details
      WHERE  sup_id = rOld.sup_id;
   END LOOP;
END;

Althugh using a cursor might not be recommended if the problem can be solved in other way, for me I found it easier to read and maintain.

Answer 2

I think good solution would be extend all primary keys by additional column which will be incremented by trigger when cloning records occure. Let's call this additional column SEC_ID. SEC_ID should by incremented and used in context of only one primary key. You don't have to create as many sequences as SEC_ID columns in tables. You can query in trigger for last sec_id for your ID and then increment it to insert in new valid primary key with original id.

Advantages:

simple mechanism

simple extending for new tables