[Oracle] The magical of Insert all

Unconditional insertion

Insert all in Oracle refers to inserting the same batch of data into different tables, if there is one requirement: Insert the data from the T table into T1,t2, and if you do not know the insert all, you may insert 2 times with INSERT, as shown here:

INSERT INTO  T1 (object_name,object_id) SELECT * from T;insert to  T2 (object_name,object_id) select * from T; Commit

In fact, the above is wrong, because in the two insert process, the data of the T table may have changed, that is to say, the data obtained by the T1,T2 table may be different, the correct wording should be the insert all:

Insert Allinto T1 (object_name,object_id) to T2 (object_name,object_id) select * from T;commit;

Conditional Insert First/all is for each row to be judged
The difference between the two:
Insert first: For each row of data, insert only the table that was established when the condition is the only one, and do not continue to check for other conditions.
Insert all: For each row of data, each when condition is checked, and the insert operation is performed if the condition is met.
Look at the following example:

--insert first--the condition in front equals 1 is included in the <=5, first means that the front is inserted and no longer inserted. Insert Firstwhen object_id = 1 Theninto T1 (object_name,object_id) when object_id <=5 then into T2 (o BJECT_NAME,OBJECT_ID) SELECT * FROM T;commit;select * from T1;object_name object_id------------------------- -----------icol$ 1select * from T2;object_name object_id---------------------                              ---------------i_user1 2con$ 3undo$ 4c_cobj# 5--insert allinsert Allwhen object_id = 1 Theninto T1 (object_name,object_id) when Obje ct_id <=5 then to 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$ 3undo$ 4c_cobj# 5

Row-to-column inserts insert all also enables row-to-column insertion:

select * from Sales_source_data; employee_id week_id sales_mon sales_tue sales_wed sales_thur sales_fri----------------------------------------- ------------------------------176 6 4000 6000insert Allint o 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) to 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 176 6 4000 176 6 176 6 6000