Hi,
I'm wondering if anyone has any suggestions of the most efficient way to achieve the following. Assume I have a simple master/detail set of tables, keyed by an ID. I want to create a copy of the master records (but assign then a new ID from a sequence) and then copy all the detail records, but link them to the "new" id.
So setting up just some basic data:
Create Sequence xxmaster_s;
Create Table xxmaster
As
Select xxmaster_s.nextval id, 'Dummy' || To_char (Level, 'fm00') val
From Dual
Connect By Level <= 10;
Create Table xxdetail
As
Select Mod(Level,10) + 1 master_id, 'Dummy Detail' || To_char (Round(Dbms_Random.Value(1,Level)), 'fm00') val_dtl
From Dual Connect By Level <= 100;
I now want to copy those master records 1-10 and their corresponding detail records, but assign them new sequences. So a "loops" way of doing this would be:
Declare
l_new_id xxmaster.id%Type;
Begin
For i In (Select id, val From xxmaster) Loop
Insert into xxmaster(id, val) Values (xxmaster_s.nextval,i.val)
Returning id Into l_new_id;
Insert Into xxdetail(master_id, val_dtl)
Select l_new_id, d.val_dtl
From xxdetail d
Where d.master_id = i.id;
End Loop;
End;
But I find it somewhat inefficient - especially if we are considering large volumes of data in xxmaster because we're looping through and inserting one record at a time, then executing the insert of detail records for each. Can I do this as two inserts perhaps, by somehow returning both the old and new values into a collection from the first insert and then using them in the second? Or is this the way others would tend to go? Anyone able to suggest a more set-based alternative?