Db2sql--insert All and insert first usage explained and examples

Source: Internet
Author: User

1, build a test tableCREATE TABLEEdw_int (Agmt_noVARCHAR2( +BYTE) not NULL, Agmt_sub_noVARCHAR2(4BYTE) not NULL, Need_repay_int Number( A,2), Curr_period Number(4) not NULL       ); CREATE TABLEedw_int_1 (Agmt_noVARCHAR2( +BYTE) not NULL, Agmt_sub_noVARCHAR2(4BYTE) not NULL, Need_repay_int Number( A,2), Curr_period Number(4) not NULL       ); CREATE TABLEedw_int_2 (Agmt_noVARCHAR2( +BYTE) not NULL, Agmt_sub_noVARCHAR2(4BYTE) not NULL, Need_repay_int Number( A,2), Curr_period Number(4) not NULL       ); 2, inserting test dataINSERT  intoEdw_int (Agmt_no, Agmt_sub_no, Need_repay_int, Curr_period)VALUES         ('20003874','2104',3126.5,7); INSERT  intoEdw_int (Agmt_no, Agmt_sub_no, Need_repay_int, Curr_period)VALUES         ('20003874','2104',3290.76,6); INSERT  intoEdw_int (Agmt_no, Agmt_sub_no, Need_repay_int, Curr_period)VALUES         ('20003874','2104',3454.06,5); INSERT  intoEdw_int (Agmt_no, Agmt_sub_no, Need_repay_int, Curr_period)VALUES         ('20003874','2104',3616.41,4); INSERT  intoEdw_int (Agmt_no, Agmt_sub_no, Need_repay_int, Curr_period)VALUES         ('20017143','2104',2350.86,0); INSERT  intoEdw_int (Agmt_no, Agmt_sub_no, Need_repay_int, Curr_period)VALUES         ('20017143','2104',3566.55,0); INSERT  intoEdw_int (Agmt_no, Agmt_sub_no, Need_repay_int, Curr_period)VALUES         ('20018273','2104',1639.46,0); INSERT  intoEdw_int (Agmt_no, Agmt_sub_no, Need_repay_int, Curr_period)VALUES         ('20018273','2104',2080.49,0); COMMIT; 3、Insert all/First Statement Example (1)、Insert all-without conditionsInsert  All        intoedw_int_1 (agmt_no,agmt_sub_no,need_repay_int,curr_period)Values(Agmt_no,agmt_sub_no,need_repay_int,curr_period) intoedw_int_2 (agmt_no,agmt_sub_no,curr_period)Values(Agmt_no,'1234', Curr_period)SelectAgmt_no,agmt_sub_no,need_repay_int,curr_period fromEdw_int; Commit; (2)、Insert all-with conditionsInsert  All        whenCurr_period=0  Then         intoedw_int_1 (agmt_no,agmt_sub_no,need_repay_int,curr_period)Values(Agmt_no,agmt_sub_no,need_repay_int,curr_period)Else       intoedw_int_2 (agmt_no,agmt_sub_no,need_repay_int,curr_period)Values(Agmt_no,agmt_sub_no,need_repay_int,curr_period)SelectAgmt_no,agmt_sub_no,need_repay_int,curr_period fromEdw_int; Commit; (3)、InsertFirst-with conditionsInsert First whenCurr_period=0  Then         intoedw_int_1 (agmt_no,agmt_sub_no,need_repay_int,curr_period)Values(Agmt_no,agmt_sub_no,need_repay_int,curr_period) whenAgmt_sub_no='2104'  Then        intoedw_int_2 (agmt_no,agmt_sub_no,need_repay_int,curr_period)Values(Agmt_no,agmt_sub_no,need_repay_int,curr_period)SelectAgmt_no,agmt_sub_no,need_repay_int,curr_period fromEdw_int; Commit; 4, Data results Viewtruncate Tableedw_int_1; truncate Tableedw_int_2; Select *  fromEdw_int; Select *  fromedw_int_1; Select *  fromedw_int_2; 5, the difference between all and first All: Do not consider the relationship, as long as the conditions are satisfied, all inserted; First: Consider the relationship, if there is data that satisfies the first when condition and satisfies the second when condition, then executes the initial then insert statement, and the second then does not insert the first then already inserted data. The difference can also be described as, all as long as the condition is satisfied, may be repeated insert; First, the condition is satisfied, then the filter is not repeated. 

Db2sql--insert All and insert first usage explained and examples

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.