CODEWITHSUNDEEP
plsql
user3447062
user3447062

Reputation: 39

Oracle PL/SQL replicate rows

let's say I got a table A with the following columns:
colA, colB, colC

I would like to iterate the records on this table and copy them into a new table B (already present in the database) with these columns: colA, colB, colC, Machine
but every record in table A should be replicated in table B as many times as the number of rows in the table MACHINES (only 1 column: NAME)

In my mind I could store the MACHINES into a collection of strings and iterate with a loop the records on table A.
On every iteration I loop in the cached MACHINES, write a single row into table B filling the MACHINE field and finally I delete the current row in table A so that it won't be processed again.

Which is the best way to achieve this task?

Upvotes: 0

Views: 364

Answers (2)

Boneist
Boneist

Reputation: 23588

Isn't that simply:

with machines as (select 'A' name from dual union all
                  select 'B' name from dual union all
                  select 'C' name from dual),
       tablea as (select 1 col1, 1 col2, 1 col3 from dual union all
                  select 2 col1, 2 col2, 2 col3 from dual union all
                  select 3 col1, 3 col2, 3 col3 from dual union all
                  select 4 col1, 4 col2, 4 col3 from dual union all
                  select 5 col1, 5 col2, 5 col3 from dual)
-- end of mimicking tables tablea and machines
select ta.col1,
       ta.col2,
       ta.col3,
       m.name
from   tablea ta
       cross join machines m;

      COL1       COL2       COL3 NAME
---------- ---------- ---------- ----
         1          1          1 A   
         2          2          2 A   
         3          3          3 A   
         4          4          4 A   
         5          5          5 A   
         1          1          1 B   
         2          2          2 B   
         3          3          3 B   
         4          4          4 B   
         5          5          5 B   
         1          1          1 C   
         2          2          2 C   
         3          3          3 C   
         4          4          4 C   
         5          5          5 C

?

Then you can delete from tableA, something like:

delete from tablea ta
where exists (select null
              from   tableb tb
              where  ta.col1 = tb.col1
              and    ta.col2 = tb.col2
              and    ta.col3 = tb.col3);

Upvotes: 2

J. Chomel
J. Chomel

Reputation: 8395

The best way to achieve row replication is as Boneist stated, to work directly with SQL sets.

This is not a good way at all to iterate and do it in a loop, like you intend to do. Especially for such a simple problem like yours.

PLSQL must not be used to try imitate what Oracle will in most cases do (far) better as you with loops.

Upvotes: 0

Related Questions