[SOLVED] Oracle sql insert into multiple tables from with clause with restriction

Oracle sql insert into multiple tables from with clause with restriction

I want to insert data into multiple tables from one query/input dataset while fetching different data with a where clause. I am using Oracle SQL Developer.

I already trid below logic which won't work:

Insert into A (X, Y, Z)
Values(Select x, y, z From inputdata where x = 1)

Insert into B (X, Y, Z)
Values(Select x, y, z From inputdata where x = 2)

Insert into C (X, Y, Z)
Values(Select x, y, z From inputdata where x = 3)

With inputdata as (Select x, y, z From source)
Select x, y, z From inputdata

Solution

Use conditional insert all like here:

create table a(x, y, z) as (select 0, 0, 0 from dual);
create table b(x, y, z) as (select 0, 0, 0 from dual);
create table c(x, y, z) as (select 0, 0, 0 from dual);

create table src(x, y, z) as (
    select 1, 1, 1 from dual union all
    select 2, 2, 2 from dual union all
    select 3, 3, 3 from dual );

insert all 
  when x = 1 then into a (x, y, z) values (x, y, z)
  when x = 2 then into b (x, y, z) values (x, y, z)
  when x = 3 then into c (x, y, z) values (x, y, z)
select * from src