Oracle/ multiple inserts without Loops

Question

I have the following table table_1 which contains thousands of rows:

Num_replication   object_name
--------------------------------
       4             obj_1
       8             obj_2
      12             obj_3

for each of one of these rows, I need to insert rows in other tables.

For instance, I have to insert one row in table_2 for each row in table_1 :

ID       obj_name
------------------
1         obj_1
2         obj_2
3         obj_3

and in table_3 I have to insert number of rows based on num_replication as following:

ID       port
--------------
1        P0001
1        P0002
1        P0003
1        P0004
2        P0001
2        P0002
2        P0003
2        P0004
2        P0005
2        P0006
2        P0007
2        P0008

and the same for other rows.

I know that I can accomplish this using loops , but I need to do it without loops , using multiple inserts.

any help would be appreciated.

score 4 · Accepted Answer · answered Mar 10 '20 at 11:05

Use hierarchical query to multiplicate rows and then conditional insert all, with dense_rank generating id:

insert all
  when column_value = 1 then
    into table_2(id, obj_name) values (rn, object_name) 
  when 1 = 1 then
    into table_3(id, port) values(rn, port)
select dense_rank() over (order by object_name) rn, t.object_name, 
       column_value, 'P'||lpad(column_value, 4, '0') port
  from table_1 t, 
  table(cast(multiset(select level 
                        from dual 
                        connect by level <= t.num_replication) 
             as sys.odcinumberlist));

^{dbfiddle demo}

Oracle/ multiple inserts without Loops

1 Answers1