How to insert multiple new records based on record(s) already in the table (in Oracle)?

Question

I want to find rows (records) which have a specific vlaue (S) in a column (Type), and insert multiple rows (e.g. 2) based on that row in the same table.

For example, in tabe t1 below, I want for each row of type 'S', 2 rows be inserted with same ID and Price, new Counter value (no specific requirement for this filed, however the Counter for records with same ID must be different), and Type will be 'B'.

It means that when inserting 2 rows based on the first record in table below (1,1200,S,200), Counter value of the new records must be different from Counter values of the records with ID=1 already in the table (1200 and 1201). So, in the initial table there were three records with Type 'S', then in the final table, for each of those records, 2 new records with Type 'B' and a new Counter value is insrted:

ID   Counter Type  Price
------------------------
1     1200    S     200
1     1201    T     400
2     1200    T     500
3     1546    S     100
3     1547    S     70
4     2607    M     250

The output table t1 will be:

ID   Counter Type  Price
------------------------
1     1200    S     200
1     1202    B     200
1     1203    B     200
1     1201    T     400
2     1200    T     500
3     1546    S     100
3     1548    B     100
3     1549    B     100
3     1547    S     700
3     1550    B     700
3     1552    B     700
4     2607    M     250

Answer 1

You need an insert select statement:

insert into t1 (ID, Counter, Type, Price)
select ID, Counter+1, 'B', Price from t1 where Type = 'S'
union all
select ID, Counter+2, 'B', Price from t1 where Type = 'S';

EDIT: Here is a statement that matches your criteria mentioned in your remark below. It gets the maximum Counter per ID and adds the count # of the added entry to the ID (1, 2, 3, ...) to it.

insert into t1 (ID, Counter, Type, Price)
select 
  ID, 
  (select max(Counter) from t1 where ID = src.ID) + row_number() over (partition by ID order by Price) as new_counter, 
  'B' as Type, 
  Price
from
(
    select ID, Price 
      from t1 
      join (select * from dual union all select * from dual) twice
      where t1.Type = 'S'
) src;

Answer 2

CREATE SEQUENCE my_SEQ

INCREMENT BY 1

START WITH 1

MINVALUE 1

MAXVALUE 999999999

NOCYCLE

NOCACHE

NOORDER;

create table MY_TABLE1 ( ID NUMBER, counter number(20), type varchar2(30), price number )

insert into my_table1 (id,counter,type,price) values (my_SEQ.nextval,1200,'S',200);

insert into my_table1 (id,counter,type,price) values (my_SEQ.nextval,1300,'B',311);

insert into my_table1 (id,counter,type,price) values (my_SEQ.nextval,200,'S',110);

insert into my_table1 (id,counter,type,price) values (my_SEQ.nextval,299,'B',329);

select * from my_table1

    ID    COUNTER TYPE                                PRICE

---

    62       1200 S                                     200
    63       1300 B                                     311
    64        200 S                                     110
    65        299 B                                     329

declare

cursor c1 is select * from My_table1 where type='B';

begin

for rec IN c1

loop

insert into my_table1 (id,counter,type,price)

values (my_SEQ.nextval,rec.counter+1,'Z',rec.price);

end loop;

end;

select * from my_table1

    ID    COUNTER TYPE                                PRICE

---

    63       1300 B                                     311
    65        299 B                                     329
    64        200 S                                     110
    62       1200 S                                     200
    66       1301 Z                                     311
    67        300 Z                                     329

6 rows selected.

So there in cursor select all rows where type is ='B' and insert them back with changed values a little! hope this helps. you could not use sequence but add rec.id+1

Answer 3

You just have to play twice this command:

insert into epn
with w(max) as
(
  select max(t.counter) from t -- useful to get max counter value
)
select t.id, w.max + rownum, 'B', t.price -- max + rownum then gives new values
from t, w
where t.type != 'B'; -- Avoid duplicating rows added 1st time

This gives:

1   1   1200    S   200
2   1   2617    B   200
3   1   2611    B   200
4   1   1201    T   400
5   1   2618    B   400
6   1   2612    B   400
7   2   1200    T   500
8   2   2613    B   500
9   2   2619    B   500
10  3   1547    S   70
11  3   2609    B   70
12  3   2615    B   70
13  3   1546    S   100
14  3   2614    B   100
15  3   2608    B   100
16  4   2607    M   250
17  4   2610    B   250
18  4   2616    B   250