Insert all, insert first, oracleinsert

Source: Internet
Author: User

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;




Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.