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