1. Insert data into multiple tables
From Oracle9i, you can use the insert statement to insert data from a table to multiple tables at the same time.
Syntax:
Insert all insert_into_clause [value_clause] subquery;
Insert conditional_insert_clause subquery;
Insert_into_clause is used to specify the insert clause;
Value_clause is used to specify a value clause;
Subquery is used to specify the subquery that provides data;
Conditional_insert_clause is used to specify the insert Condition Clause.
Example 1: Use the all operator to insert multiple tables
When the all operator is used to insert multiple tables, the subquery after the into clause must be executed in each Condition Clause.
Insert all
When deptno = 10 then into dept10
Else into dept20
Select * from Dept;
Note: After the preceding insert statement is executed, the employee information of Department 10 is inserted into the dept10 table.
Example 2: Use the first operator to insert multiple tables
When you use the first operator to insert multiple tables, if the data already meets the previous conditions and has been inserted into a table, the row of data will not be used in subsequent inserts.