• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1154
  • Last Modified:

Inserting Parent/Child Records from a Copy in the same Tables (Oracle)

I need to select a set of parent/child data from two tables, then insert it into the same tables with identical values, except the ID columns will change.  Can this be done with one SQL statement, or will multiple SQL's or a short program be required?

See example data below.  P_ID is the columns that ties the two tables together.  I need to duplicate the data exactly, but update the P_ID and C_ID columns.  

This is using Oracle 10g.
Table P (the parent)
P_ID	VALUEA	VALUEB
1000	11A	99
1001	11B	98
 
Table C (the child)
C_ID	P_ID	VALUE1
200000	1000	AA
200001	1000	AB
200002	1000	AC
200003	1001	AD
200004	1001	AE
200005	1001	AF

Open in new window

0
robert1330
Asked:
robert1330
  • 5
  • 3
  • 2
  • +2
1 Solution
 
flow01Commented:
An insert statement effects 1 table , so you need 2.
For keeping the pid in both tables alike your need the old value of pid in an extra intermediate table  or use a program.
In which way are the new id's assigned ?
0
 
ajexpertCommented:
Do you main tain sequences for P_ID's(for parent) and C_ID's(for child)?
0
 
lilian-arnaudCommented:
What I did before was to insert the parent and the child with P_ID * -1.

In this case, your PKs are not violated.




-- the sql script is trivial for this first part :
 
insert into P(P_ID,VALUEA,VALUEB)
Select -P_ID,VALUEA,VALUEB
  from P;
 
insert into C(C_ID,P_ID,VALUE1)
select -C_ID,-P_ID,VALUE1
  from C;
 
-- in your case, you'll get :
 
Table P (the parent)
P_ID     VALUEA  VALUEB
-1000    11A     99
-1001    11B     98
 
Table C (the child)
C_ID      P_ID    VALUE1
-200000  -1000    AA
-200001  -1000    AB
-200002  -1000    AC
-200003  -1001    AD
-200004  -1001    AE
-200005  -1001    AF
 
/*
After the insert, 
 
you have to execute a PL/SQL Statement in order to change the values of C_ID and P_ID :
*/
 
begin
   for x in (Select c_ID from C where C_ID < 0)
   loop
        update C
           set C_ID = sequenceC.nextVal
      where C_ID = X.C_ID;
  end loop;
end;
/
 
-- In order to change the value of P_ID, you have to disable your
-- foreign key constraint between P and C (if it exists).
 
alter table C disable constraint  [constraint_name];
 
begin
   for x in (Select p_ID from P where P_ID < 0)
   loop
        update P
           set P_ID = sequenceC.nextVal
      where P_ID = X.P_ID;
 
        update C
           set P_ID = sequenceC.currVal
      where P_ID = X.P_ID;
  end loop;
end;
/
 
-- enable again the FK.
alter table C enable constraint  [constraint_name];

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
lilian-arnaudCommented:
sorry, the second PL/SQL must use the sequence for the table P (sequenceP) and not sequenceC !
0
 
lilian-arnaudCommented:
the second solution, without disabling/enabling the FK is to perform the job in a single PL/SQL treatment


declare
   max_p_id P.p_id%type;
begin
   -- max ID to copy from the table P.
  select max(p_id) 
    into max_p_id 
    from P;
  -- loop around all rows to copy
  for curP in (Select * from P where p_id <= max_p_id)
  loop
    -- insert the new parent 
    -- (I assume you use a sequence for the column P_ID)
    insert into P(P_ID,VALUEA,VALUEB)
     values (sequenceP.nextval,curP.VALUEA,curP.VALUEB);
    -- then insert the children
    insert into C(C_ID,P_ID,VALUE1)
    select sequenceC.currval,sequenceP.currval,VALUE1
  from C
where p_id = curP.p_id;
end loop;
end;
/

Open in new window

0
 
ajexpertCommented:
If you are maintaining sequences, it can be done without PL/SQL.  Just simple SQL will do.
0
 
SharathData EngineerCommented:
Which table you want to update? Can you post your expected result after update?
0
 
robert1330Author Commented:
Below is another copy of the data, this time with the expected results.  Just to clarify, there is a FK relationship between P_ID of both tables.  For those that have responded, please take a look and let me know if your response still works (or add an updated response).  Thans for all the help!

Table P (the parent)
P_ID	VALUEA	VALUEB
------------------------------BEFORE
1000	11A	99
1001	11B	98
-----------------------------AFTER 
1000	11A	99
1001	11B	98
1002	11A	99 (new)
1003	11B	98 (new)
 
Table C (the child)
C_ID	P_ID	VALUE1
---------------------------BEFORE
200000	1000	AA
200001	1000	AB
200002	1000	AC
200003	1001	AD
200004	1001	AE
200005	1001	AF
-----------------------------AFTER
200000	1000	AA
200001	1000	AB
200002	1000	AC
200003	1001	AD
200004	1001	AE
200005	1001	AF
200006	1002	AA (new)
200007	1002	AB (new)
200008	1002	AC (new)
200009	1003	AD (new)
200010	1004	AE (new)
200011	1005	AF (new)

Open in new window

0
 
lilian-arnaudCommented:
the answer is yes for my 2 solutions.

in the first case, you need to know the name of the constraint and the name for the sequences,
in the second case, you just need to know the name  of the sequences.
0
 
robert1330Author Commented:
lilian: It looks good, but I have a couple of questions.  I forgot to mention that there is other data in both tables that will NOT be copied.  I am selecting a set of data based on particular criteria, so this line would not work, because I don't want ALL records less than max_p_id:

          for curP in (Select * from P where p_id <= max_p_id)

Also, for the insert into the Child table (below), should't sequenceP.currval be sequenceP.nextval instead?

insert into C(C_ID,P_ID,VALUE1)
    select sequenceC.currval,sequenceP.currval,VALUE1

Also, my table examples were quite simplified.  The actual tables have millions of records, and the copying/inserting will involve 20,000 records at a time.  If this makes a difference with your solution, let me know.  Thanks!
0
 
lilian-arnaudCommented:
okay,

for the first filter, just add it in the where clause (keep the max)

  for curP in (Select * from P where p_id <= max_p_id AND VALUE1 like ....)

you're right and wrong for the second insert

the correct statement is :

insert into C(C_ID,P_ID,VALUE1)
    select sequenceC.nextval,sequenceP.currval,VALUE1
    from ...


because we need a new ID (nextval) for C_ID, not P_ID, which have to contain the same value than the parent table.

for the size, I don't think you will have a issue for 20'000 new rows if your tables contain millions of record, don't worry about that.

cheers !
0
 
robert1330Author Commented:
Thanks for the detailed and timely repsonses!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now