Inserting records into multiple tables at the same time using the Insert Select implementation

Inserting records into multiple tables at the same time using the Insert Select implementation



One, unconditional INSERT all


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


INSERT All


Insert_into_clause Values_clause_1


[Insert_into_clause values_clause_2]


......


subquery;


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


1, specifies all following the multiple table Insert_into_clauses to execute the unconditional multiple table inserts;


2. The Oracle server executes each insert_into_clause once for each row returned by the subquery.





Second, the condition inserts all


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


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;


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


1. Specify conditional_insert_clause to perform a condition multiple table insertion;


2, the Oracle server through the corresponding when conditions filter every insert_into_clause, determine whether to implement this insert_into_clause;


3, a single multiple table INSERT statement can contain up to 127 when clauses.





Third, the condition inserts the


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


INSERT A


When condition THEN insert_into_clause values_clause


[When condition THEN] [Insert_into_clause Values_clause]


......


[ELSE] [Insert_into_clause Values_clause]


subquery;


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


1, the Oracle server for each occurrence in the statement order of the When clause evaluation;


2. If the value of the first when clause is the True,oracle server executes the corresponding INTO clause for the given row, and skips over the later when clause (the later when statement no longer considers the record that satisfies the first when clause, even if the record satisfies the condition in the When statement).





NOTE: Constraints on multiple table INSERT statements


A, you can only in the table and not on the view to perform multiple table inserts;


b, you can't perform a multiple table insert into a remote table;


c, you cannot specify a table collection expression when performing a multiple-table insert;


D, in a multiple table insert, all insert_into_clauses cannot be combined to specify more than 999 rows;


E, rollback works only if the table data in all insert_into_clauses 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 are empty.





Test one: 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;





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 two: 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)


4 Else into Z_TEST3 values (name)


5 Select Id,name from Z_test;





Ten 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 three: Condition INSERT a


SQL statement:


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


Sql> Insert A


2 when id=1 then into z_test1 values (id,name)


3 when id>5 then into Z_TEST2 values (ID)


4 Else into 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.