[Oracle] the use of InsertAll

Unconditional insert

Insert all in Oracle refers to inserting the same batch of data into different tables. If there is a need to insert the data in table t into t1 and t2 separately, if you do not know insert all, you may insert data twice using insert, as shown below:

insert  into t1(object_name,object_id) select * from t;insert  into t2(object_name,object_id) select * from t;commit;

In fact, the preceding statement is incorrect, because the data in the t table may have changed during the two insert operations, that is, t1, the data obtained from table t2 may be different. The correct syntax should be insert all:

insert allinto t1(object_name,object_id)into t2(object_name,object_id)select * from t;commit;

Conditional insert first/all is used to judge each row.
Differences:
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.
See the following example:

-- Insert first -- if the condition with the FIRST value equal to 1 is included in <= 5, "first" indicates that the FIRST condition is inserted, and the first condition is not inserted. Insert firstwhen object_id = 1 theninto t1 (object_name, object_id) when object_id <= 5 then into t2 (object_name, object_id) select * from t; commit; select * from t1; OBJECT_NAME OBJECT_ID example --- ICOL $1 select * from t2; OBJECT_NAME OBJECT_ID example --- I _USER1 2CON $3 UNDO $ 4C_COBJ #5 -- insert allinsert allwhen object_id = 1 theninto t1 (object_name, object_id) when object_id <= 5 then into t2 (object_name, object_id) select * from t; commit; SQL> select * from t1; OBJECT_NAME OBJECT_ID ------------------------------- --- ICOL $ 1SQL> select * from t2; OBJECT_NAME OBJECT_ID --------------------------------- --- ICOL $1I_USER1 2CON $3 UNDO $ 4C_COBJ #5

Insert all can also insert rows to columns:

select * from sales_source_data;EMPLOYEE_ID    WEEK_ID  SALES_MON  SALES_TUE  SALES_WED SALES_THUR  SALES_FRI----------- ---------- ---------- ---------- ---------- ---------- ----------        176          6       2000       3000       4000       5000       6000insert allinto sales_info values(employee_id,week_id,sales_mon)into sales_info values(employee_id,week_id,sales_tue)into sales_info values(employee_id,week_id,sales_wed)into sales_info values(employee_id,week_id,sales_thur)into sales_info values(employee_id,week_id,sales_fri)select employee_id,week_id,sales_mon,sales_tue,sales_wed,sales_thur,sales_frifrom sales_source_data;select * from sales_info;EMPLOYEE_ID       WEEK      SALES----------- ---------- ----------        176          6       2000        176          6       3000        176          6       4000        176          6       5000        176          6       6000

Restrictions on multi-table insert statements 1. You can only execute multi-table insert statements on tables, but not on views or materialized views;
2. The multi-table insert statement cannot be executed on the remote table;
3. Table set expressions cannot be used;
4. The number of target Columns cannot exceed 999;
5. In the RAC environment or when the target table is an indexed organization table or the target table has a BITMAP index, the multi-Table insertion statement cannot be executed in parallel;
6. Multi-table insert statements do not support execution plan stability;
7. subqueries in Multi-table insert statements cannot use sequences.