Insert select is used to insert records into multiple tables at the same time.

Insert select is used to insert records into multiple tables at the same time.

I. Unconditional insert all

Bytes ---------------------------------------------------------------------------------------------

Insert all

Insert_into_clause values_clause_1

[Insert_into_clause values_clause_2]

......

Subquery;

Bytes ----------------------------------------------------------------------------------------------

1. specify all the following multi-Table insert_into_clses to execute unconditional multi-table insert;

2. For each row returned by the subquery, the Oracle server executes each insert_assist_clause once.

Ii. Conditional insert all

Bytes ---------------------------------------------------------------------------------------------

Insert all

When condition then insert_into_clause values_clause

[When condition then] [Insert_into_clause values_clause]

......

[Else] [Insert_into_clause values_clause]

Subquery;

Bytes --------------------------------------------------------------------------------------------

1. Specify conditional_insert_clause to execute a conditional multitable insert statement;

2. the Oracle server uses the corresponding when condition to filter each insert_into_clause and determine whether to execute this insert_into_clause;

3. A single multi-table insert statement can contain a maximum of 127 when clauses.

Iii. condition insert first

Bytes --------------------------------------------------------------------------------------------

Insert first

When condition then insert_into_clause values_clause

[When condition then] [Insert_into_clause values_clause]

......

[Else] [Insert_into_clause values_clause]

Subquery;

Bytes --------------------------------------------------------------------------------------------

1. the Oracle server evaluates every when clause that appears in the order of statements;

2. If the value of the first when clause is true, the Oracle server executes the corresponding into clause for the given row and skips the subsequent when clause.(The following when statements do not consider satisfying the record of the first when clause, even if the record meets the conditions in the when statement).

Note: constraints on multi-table insert statements

A. You can only insert multiple tables in a table but not in a view;

B. You cannot insert multiple tables into a remote table;

C. When executing a multi-table insert operation, you cannot specify a table set expression;

D. In a multi-table insert operation, you cannot specify more than 999 object columns for all insert_into_clures;

E. rollback takes effect only when the table data in all insert_into_clses is not updated.

Eg:

Tables:Z_test(Id int,Name varchar2(10));

Z_test1(Id int,Name varchar2(10));

Z_test2(Id int);

Z_test3(Name varchar2(10);

Initial Data: z_test

ID name

10 133

5 184

1 18423

1 18445

1 18467

6 129

2 12923

2 12945

Z_test1,Z_test2,Z_test3 is empty.

Test 1: unconditional insert all

SQL statement:

----------------------------------------------------------------------------

SQL>Insert all

2 into z_test1(ID,Name)Values(ID,Name)

3 into z_test2(ID)Values(ID)

4 select ID,Name from z_test;

16 rows created.

----------------------------------------------------------------------------

Test results:

----------------------------------------------------------------------------

SQL>Select*From z_test1;

ID name

------------------------------

10 133

5 184

1 18423

1 18445

1 18467

6 129

2 12923

2 12945

8 rows selected.

SQL>Select*From z_test2;

ID

----------

10

5

1

1

1

6

2

2

8 rows selected.

----------------------------------------------------------------------------

Test 2: condition insert all

SQL statement:

----------------------------------------------------------------------------

SQL>Insert all

2 when id > 5 then into z_test1 ( id , name ) values ( id , name )

3 when ID<>2 then into z_test2(ID)Values(ID)

4ElseInto z_test3 values(Name)

5 select ID,Name from z_test;

10 rows created.

----------------------------------------------------------------------------

Test results:

----------------------------------------------------------------------------

SQL>Select*From z_test1;

ID name

------------------------------

10 133

6 129

SQL>Select*From z_test2;

ID

----------

10

5

1

1

1

6

6 rows selected.

SQL>Select*From z_test3;

Name

--------------------

12923

12945

2 rows selected.

----------------------------------------------------------------------------

Test 3: condition insert first

SQL statement:

----------------------------------------------------------------------------

SQL>Insert first

2 when ID=1 then into z_test1 values(ID,Name)

3 when ID>5 then into z_test2 values(ID)

4ElseInto z_test3 values(Name)

5 select*From z_test;

8 rows created.

----------------------------------------------------------------------------

Test results:

----------------------------------------------------------------------------

SQL>Select*From z_test1;

ID name

------------------------------

1 18423

1 18445

1 18467

3 rows created.

SQL>Select*From z_test2;

ID

----------

10

6

2 rows created.

SQL>Select*From z_test3;

Name

--------------------

184

12923

12945

3 rows created.