Insert all, insert first, oracleinsert
Using insert first/all, the INSERT statement can insert multiple tables at the same time. You can also determine the table or table to which each record is inserted based on the judgment conditions.
Insert first:For each row of data, only the table with the first when condition is inserted, and other conditions are not checked.
Insert all: For each row of data, check each when condition. If the condition is met, perform the insert operation.
create table edw_int ( agmt_no varchar2(40 byte) not null, agmt_sub_no varchar2(4 byte) not null, need_repay_int number(22,2), curr_period number(4) not null ); create table edw_int_1 ( agmt_no varchar2(40 byte) not null, agmt_sub_no varchar2(4 byte) not null, need_repay_int number(22,2), curr_period number(4) not null ); create table edw_int_2 ( agmt_no varchar2(40 byte) not null, agmt_sub_no varchar2(4 byte) not null, need_repay_int number(22,2), curr_period number(4) not null );
insert into edw_int (agmt_no, agmt_sub_no, need_repay_int, curr_period) values ('20003874', '2104', 3126.5, 7);insert into edw_int (agmt_no, agmt_sub_no, need_repay_int, curr_period) values ('20003874', '2104', 3290.76, 6);insert into edw_int (agmt_no, agmt_sub_no, need_repay_int, curr_period) values ('20003874', '2104', 3454.06, 5);insert into edw_int (agmt_no, agmt_sub_no, need_repay_int, curr_period) values ('20003874', '2104', 3616.41, 4);insert into edw_int (agmt_no, agmt_sub_no, need_repay_int, curr_period) values ('20017143', '2104', 2350.86, 0);insert into edw_int (agmt_no, agmt_sub_no, need_repay_int, curr_period) values ('20017143', '2104', 3566.55, 0);insert into edw_int (agmt_no, agmt_sub_no, need_repay_int, curr_period) values ('20018273', '2104', 1639.46, 0);insert into edw_int (agmt_no, agmt_sub_no, need_repay_int, curr_period) values ('20018273', '2104', 2080.49, 0);COMMIT;
Insert all example
insert all into edw_int_1 (agmt_no, agmt_sub_no, need_repay_int, curr_period) values (agmt_no, agmt_sub_no, need_repay_int, curr_period) into edw_int_2 (agmt_no, agmt_sub_no, curr_period) values (agmt_no, '1234', curr_period) select agmt_no, agmt_sub_no, need_repay_int, curr_period from edw_int;commit;
After deleting the data, continue the test and add the condition when then else.
Truncate table edw_int_1;
Truncate table edw_int_2;
insert all when curr_period = 0 then into edw_int_1 (agmt_no, agmt_sub_no, need_repay_int, curr_period) values (agmt_no, agmt_sub_no, need_repay_int, curr_period) else into edw_int_2 (agmt_no, agmt_sub_no, need_repay_int, curr_period) values (agmt_no, agmt_sub_no, need_repay_int, curr_period) select agmt_no, agmt_sub_no, need_repay_int, curr_period from edw_int;commit;
Delete data test insert first
insert first when curr_period = 0 then into edw_int_1 (agmt_no, agmt_sub_no, need_repay_int, curr_period) values (agmt_no, agmt_sub_no, need_repay_int, curr_period) when agmt_sub_no = '2104' then into edw_int_2 (agmt_no, agmt_sub_no, need_repay_int, curr_period) values (agmt_no, agmt_sub_no, need_repay_int, curr_period) select agmt_no, agmt_sub_no, need_repay_int, curr_period from edw_int;commit;