Oracle Multi-Table INSERT statement insert All/insert First

Source: Internet
Author: User

about insert all and insert
First

One, unconditional INSERT all
Ii. Conditions INSERT All
Three, conditions INSERT
First

Insert ... Select
Inserting records into multiple tables with the Insert Select implementation
One, unconditional INSERT all

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

INSERT All

Insert_into_clause Values_clause_1

[Insert_into_clause values_clause_2]
......

subquery;

----------------------------------------------------------------------------------------------
1. Specify all the following multi-table insert_into_clauses to perform unconditional multi-table insertion;

2. For each row returned by a subquery, Oracle
The server executes each insert_into_clause once.



Ii. Conditions INSERT 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 insert;

2, the Oracle server through the corresponding when
Conditions filter each Insert_into_clause to determine whether to perform this insert_into_clause;


3. A single multiple-table INSERT statement can contain a maximum of 127 when clauses.


Third, conditions INSERT first

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

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;

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

1. The Oracle server evaluates each of the When clauses appearing in the statement order;

2. If the value of the first when clause is true,oracle
The server executes the appropriate into clause for the given row, and skips the back when
Clause (the subsequent 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: Multi-table INSERT
Constraints on a statement

A, you can only perform multiple table insertions on the table but not on the view;

b, you cannot perform a multi-table insert into a remote table;


C, when performing a multiple-table insert, you cannot specify a table set expression;

D, in a multi-table insert, all the insert_into_clauses
Can not be combined to specify more than 999 mesh columns;

E. Rollback will only work if the table data in all insert_into_clauses has not been updated.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.