Best Approach for Cloning database records using SPs

Question

Hi,
I have to write code to clone a database entry with associated data in other tables and assign it a new ID. Simplified I have a MAIN Table with a key of ID and sub table say SUB1 with FK of ID and multiple records for each entry in MAIN.
I want to copy the data for a specific ID in MAIN to new records updating the ID to a new value to allow the existing entry to remain as a snapshot in time and the new entry to be a new work in progress.

I am looking to use Stored Procedures and am wondering if its possible/advisable to have a highlevel SP that invokes other SP's to carry out the work?

e.g.

    CREATE PROCEDURE CopyNewVersion (IN oldID)
    ...
    BEGIN
    --copy main record details for passed in oldID, 
    --return the new ID thats been allocated
      CALL CopyNewMainRecord(IN oldID, OUT newID )
    --copy all SUB1 records for oldID to newID
      CALL CopyNewSub1Records(IN oldID, IN newID)
    --Declare a cursor to return the details in MAIN for newID
    END

I'm seeing the CopyNewSub1Records as something like

    CREATE PROCEDURE CopyNewSub1Records (IN oldID, IN NewID)
    ...
    BEGIN
      --select all records in SUB1 with FK oldID
      --sp opens a cursor for return 
      CALL GetSUB1Records(oldID)
      for each returned record in the cursor resultset
        --insert into SUB1 values(newID, other data for this row,....)
        CALL CreateSUB1Record(row details)
    END

So my question is, is it OK to have the OUT of newID from CopyNewMainRecord as in IN to CopyNewSub1Records and can I use the resultset with multiple rows from the Get SP in CopyNewSub1Records to loop through while calling the insert SP?

I am currently awaiting being granted rights to create SP's on our DB2 environment by the DB Admin so thats why I'm asking rather than attempting this.

Answer 1

I'm not too familiar with DB2, but I would think that direct inserts would work a lot better than trying to work with a cursor across scopes.

Having the output parameter for the copy main stored procedure as an input into the other stored procedure(s) makes perfect sense to me.

As an example of how I would create CopyNewSub1Records, I would do something like this: (please excuse any syntax problems... like I said, I don't use DB2 - how do you know a parameter from a column?)

CREATE PROCEDURE CopyNewSub1Records (IN oldID, IN NewID)
BEGIN
     INSERT INTO Sub1
     (
          ID,
          SubID,
          Col1,
          Col2
     )
     SELECT
          NewID,
          SubID,
          Col1,
          Col2
     FROM
          Sub1
     WHERE
          ID = oldID
END

Answer 2

I've never used DB2, but doesn't it support triggers?

Sounds like what your are looking to do is build an audit table. I've done this in SQL Server before, and done correctly, it can be quite useful. You should be able to find some good info by googling "DB2 Audit Trigger" or something similar.