Copying table values with dependent child records

Question

If you have a pair of tables with a numeric primary key and a parent-child relationship, what's the best way of copying records in these tables, given that if you do it as two separate inserts, the key for the parent will have changed?

Perhaps best explained with an example. Say you had the following table structure:

CREATE TABLE APARENT (
  APARENT_CODE NUMBER, -- primary key
  AVALUE1 NUMBER,
  AVALUE2 NUMBER
);

CREATE TABLE ACHILD (
  ACHILD_CODE NUMBER,  -- primary key
  APARENT_CODE NUMBER, -- foreign key
  AVALUE3 NUMBER
);

The values in APARENT are parent records. They may have many records in the ACHILD table, linked back to them by the APARENT_CODE field.

What's the best way of copying a record (which includes a single parent and perhaps multiple child records)? My confusion arises because when you insert the duplicate records into the parent table you necessarily have to change the primary key value (in this case through a sequence) so when you insert the child records, how do you know which foreign key value to link them to?

Edit: Sorry, editing in case this isn't clear: The first insert is copying multiple records, so CURRVAL cannot be used in the second insert.

At the moment I'm using this code:

CREATE TABLE TMP_CODES (
  OLD_APARENT_CODE NUMBER,
  NEW_APARENT_CODE NUMBER
);

INSERT INTO TMP_CODES
  SELECT APARENT_CODE,
         SQ_APARENT.NEXTVAL -- gets new sequence value
    FROM APARENT
   WHERE AVALUE1 = 10;

INSERT INTO APARENT
  SELECT C.NEW_APARENT_CODE, -- the copied parent records get a new sequence value
         A.VALUE1,
         A.VALUE2
    FROM APARENT A,
         TMP_CODES C
   WHERE A.AVALUE1 = 10
     AND C.OLD_APARENT_CODE = A.APARENT_CODE;

INSERT INTO ACHILD
  SELECT SQ_ACHILD.NEXTVAL,  -- a new sequence value for the primary key
         C.NEW_APARENT_CODE, -- this inserts the correct link value
         A.VALUE3
    FROM ACHILD A,
         TMP_CODES C
   WHERE C.OLD_APARENT_CODE = A.APARENT_CODE;

I'm just wondering if there's a more efficient way of doing it, rather than expressing all the codes into a temporary table first.

I'm using Oracle 10. Thanks for any help.

Answer 1

The usual way to insert a row and get the generated sequence value in a single statement is to use the RETURNING INTO clause. Unfortunately, this clause is not supported with the multi-row INSERT SELECT statement (as of 11.2.0.2.0).

So you will have to either:

• use a cursor and insert in the parent table row-by-row (then use one multi-row insert in the child table for each row in the parent table).

  For instance, in your case:

  DECLARE
     l_key aparent.aparent_code%TYPE;
  BEGIN
     FOR cc IN (SELECT a.aparent_code, a.value1, a.value2 
                  FROM aparent a 
                 WHERE a.avalue1 = 10) LOOP
        INSERT INTO aparent 
           VALUES (sq_aparent.nextval, cc.value1, cc.value2) 
           RETURNING aparent_code INTO l_key; -- get the new parent key
        INSERT INTO achild 
           (SELECT sq_achild.nextval, l_key, value3 -- use the new parent key
              FROM achild 
             WHERE aparent_code = cc.aparent_code);
     END LOOP;
  END;
  

• use multi-row inserts with a workaround as you did.

I think your approach may be the most efficient generally, especially if many parent rows are being copied. The overhead of inserting into the temporary table and joining to it should be minimal compared to the large overhead incurred by many single-row statements.

Answer 2

The interim table isn't necessary if you use NEXTVAL and CURRVAL for the parent table's sequence.

INSERT INTO APARENT
  SELECT SQ_APARENT.NEXTVAL,
         A.VALUE1,
         A.VALUE2
    FROM APARENT 
   WHERE A.AVALUE1 = 10

INSERT INTO ACHILD
  SELECT SQ_ACHILD.NEXTVAL,  -- a new sequence value for the primary key
         SQ_APARENT.CURRVAL, -- this inserts the correct link value
         A.VALUE3
    FROM ACHILD A
   WHERE A.APARENT_CODE = 10;