INSERT SELECT loop

Question

I am trying to transfer data from one table to another. But in the process I need to do something extra I am just wondering is it possible to do something like this in SQL or PL/SQL alone.

      source                    target
-------------------       ------------------------
| id | name | qty |       | id | source_id | qty |
-------------------       ------------------------
| 1  | test | 2   |       | 1  | 1         | 1   |
-------------------       ------------------------
| 2  | ago  | 1   |       | 2  | 1         | 1   |    
-------------------       ------------------------
                          | 3  | 2         | 1   |
                          -----------------------

Here based on the quantity in source table I will have to insert multiple records. Quantity could be of any number. ID in target table is auto incremented. I tried this

INSERT INTO target (SELECT id, qty FROM source);

But this does not take care of the qty loop.

Answer 1

Plain SQL:

with
     inputs ( id, qty ) as (
       select 1, 2 from dual union all
       select 2, 1 from dual union all
       select 3, 5 from dual
     )
-- end of test data; solution (SQL query) begins below this line
select row_number() over (order by id) as id, id as source_id, 1 as qty
from   inputs
connect by level <= qty
       and prior id = id
       and prior sys_guid() is not null
;

NOTE - if the id is generated automatically, just drop the row_number().... as id column; the rest is unchanged.

ID  SOURCE_ID  QTY
--  ---------  --
 1          1   1
 2          1   1
 3          2   1
 4          3   1
 5          3   1
 6          3   1
 7          3   1
 8          3   1

Answer 2

This is possible using SQL. Use CTE to generate amount of rows that matches your maximum qty from source table and use non-equi JOIN for generating rows. Use row_number analytic function to assign each row it's unique id (if you have it in your target table, check below on my Edit):

with gen_numbers(r) as (
select rownum r
from dual
connect by rownum <= (select max(qty) from src) -- our maximum limit of rows needed
)
select
  row_number() over (order by src.id) as id,
  src.id as source_id, 
  1 as qty
from src
join gen_numbers on src.qty <= gen_numbers.r; -- clone rows qty times

Note that you can safely put in constans value 1 in the output of qty.

---

Your test data:

create table src (id int, name varchar(255), qty int);
insert into src (id, name, qty) 
  select 1, 'test', 2 from dual union all
  select 2, 'ago',  1 from dual
  ;

Result:

ID  SOURCE_ID   QTY
1   2           1
2   2           1
3   1           1

---

Edit: Since your target id column is auto incremented, you don't need the row_number. Just specify it like that to perform an INSERT:

with gen_numbers(r) as (
select rownum r
from dual
connect by rownum <= (select max(qty) from src) -- our maximum limit of rows needed
)
insert into target_table(source_id, qty)
select
  src.id as source_id, 
  1 as qty
from src
join gen_numbers on src.qty <= gen_numbers.r; -- clone rows qty times
order by src.id

Notice that I've added an ORDER BY clause to ensure proper ordering of inserting values.

Answer 3

INSERT INTO TARGET(source_id, qty)
    WITH
    output
    AS
    (
      SELECT id, qty FROM source
      UNION ALL
      SELECT id, qty - 1 FROM source WHERE qty > 1
    )    
    SELECT
    id, count(*) as qty 
    FROM output
    group by 
    id, quantity
    ORDER BY
    id