The insert all statement allows to insert data from a subquery into multiple tables.
create table tq84_insert_all (
a number,
b varchar2(10)
);
insert all
into tq84_insert_all values (1, 'one' )
into tq84_insert_all values (2, 'two' )
into tq84_insert_all values (3, 'three')
select 1 from dual;
select * from tq84_insert_all;
drop table tq84_insert_all purge;
create table tq84_insert_all_dest_en (
id number,
txt varchar2(10)
);
create table tq84_insert_all_dest_de (
id number,
txt varchar2(10)
);
create table tq84_insert_all_src (
id number,
en varchar2(10),
de varchar2(10)
);
insert into tq84_insert_all_src values (1, 'one' , 'eins');
insert into tq84_insert_all_src values (2, 'two' , 'zwei');
insert into tq84_insert_all_src values (3, 'three', 'drei');
insert into tq84_insert_all_src values (4, 'four' , 'vier');
insert into tq84_insert_all_src values (5, 'five' , 'fünf');
insert all
into tq84_insert_all_dest_en (id, txt) values (id, en)
into tq84_insert_all_dest_de (id, txt) values (id, de)
select
*
from tq84_insert_all_src;
select * from tq84_insert_all_dest_en;
-- ID TXT
-- ---------- ----------
-- 1 one
-- 2 two
-- 3 three
-- 4 four
-- 5 five
select * from tq84_insert_all_dest_de;
-- ID TXT
-- ---------- ----------
-- 1 eins
-- 2 zwei
-- 3 drei
-- 4 vier
-- 5 fünf
drop table tq84_insert_all_src purge;
drop table tq84_insert_all_dest_en purge;
drop table tq84_insert_all_dest_de purge;
The when clause specifies a condition which must be met to execute the insert statement.
create table tq84_src (
valid_from date not null,
valid_to date not null,
item varchar2(10)
);
insert into tq84_src values (date '0001-01-01', date '9999-12-31', 'both' );
insert into tq84_src values (date '1990-01-01', date '1995-12-31', 'none' );
insert into tq84_src values (date '2015-01-01', date '2015-12-31', '2015 only');
insert into tq84_src values (date '2016-01-01', date '2016-12-31', '2016 only');
create table tq84_dst_2015_12_31 as select * from tq84_src where 1 = 0;
create table tq84_dst_2016_01_01 as select * from tq84_src where 1 = 0;
-- select * from tq84_src where date '2015-12-31' between valid_from and valid_to;
-- select * from tq84_src where date '2016-01-01' between valid_from and valid_to;
insert all
when date '2015-12-31' between valid_from and valid_to then into tq84_dst_2015_12_31
when date '2016-01-01' between valid_from and valid_to then into tq84_dst_2016_01_01
select * from
tq84_src;
select * from tq84_dst_2015_12_31;
-- VALID_FR VALID_TO ITEM
-- -------- -------- ----------
-- 01.01.01 31.12.99 both
-- 01.01.15 31.12.15 2015 only
select * from tq84_dst_2016_01_01;
-- VALID_FR VALID_TO ITEM
-- -------- -------- ----------
-- 01.01.01 31.12.99 both
-- 01.01.16 31.12.16 2016 only
drop table tq84_dst_2016_01_01 purge;
drop table tq84_dst_2015_12_31 purge;
create table tq84_dst_2015_12_31 (valid_from date, item varchar2(10));
create table tq84_dst_2016_01_01 (valdi_to date, item varchar2(10));
insert all
when date '2015-12-31' between valid_from and valid_to then into tq84_dst_2015_12_31 values (valid_from, item)
when date '2016-01-01' between valid_from and valid_to then into tq84_dst_2016_01_01 values (valid_to , item)
select * from
tq84_src;
select * from tq84_dst_2015_12_31;
-- VALID_FR ITEM
-- -------- ----------
-- 01.01.01 both
-- 01.01.15 2015 only
select * from tq84_dst_2016_01_01;
-- VALDI_TO ITEM
-- -------- ----------
-- 31.12.99 both
-- 31.12.16 2016 only
drop table tq84_dst_2016_01_01 purge;
drop table tq84_dst_2015_12_31 purge;
drop table tq84_src purge;
create table tq84_src (
id integer,
txt varchar2(10)
);
begin
insert into tq84_src values (1, 'A' );
insert into tq84_src values (2, 'B' );
insert into tq84_src values (3, 'C' );
insert into tq84_src values (1, 'aaa');
insert into tq84_src values (2, 'bb' );
insert into tq84_src values (1, 'aa' );
commit;
end;
/
create table tq84_dest_id (id integer primary key);
create table tq84_dest_txt (txt varchar2(10), id references tq84_dest_id);
insert all
when rn = 1 then into tq84_dest_id (id ) values (id )
when 1 = 1 then into tq84_dest_txt(id, txt) values (id, txt)
select
row_number() over (partition by id order by null) rn,
id,
txt
from
tq84_src;
select * from tq84_dest_id;
select * from tq84_dest_txt;
Cleaning up:
drop table tq84_src;
drop table tq84_dest_txt;
drop table tq84_dest_id;
ELSE clause and multiple inserts per match
create table tq84_d (id number, tx varchar2(10));
create table tq84_s (id number, tx varchar2(10));
begin
insert into tq84_s values (1, 'a');
insert into tq84_s values (2, 'b');
insert into tq84_s values (3, 'c');
commit;
end;
/
insert all
when id = 1 then into tq84_d values (4, 'd')
into tq84_d values (5, 'e')
else into tq84_d values (id, tx)
select
*
from
tq84_s;
drop table tq84_d;
drop table tq84_s;