Introduction to Oracle insert all statements

Source: Internet
Author: User

Introduction to Oracle insert all statements

In Oracle, the insert ALL statement is described as follows:

1. insert all unconditionally

Create table t1 (product_id NUMBER, product_name VARCHAR2 (80), month number );

Insert into t1 VALUES (111, 'apple', 1 );
Insert into t1 VALUES (222, 'orange', 1 );
Insert into t1 VALUES (333, 'Banana ', 1 );

COMMIT;

Create table t2 as select * FROM t1 WHERE 1 = 2;

INSERT ALL
INTO t2
VALUES (product_id, product_name, MONTH)
INTO t2
VALUES (product_id, product_name, MONTH + 1)
INTO t2
VALUES (product_id, product_name, MONTH + 2)
INTO t2
VALUES (product_id, product_name, MONTH + 3)
SELECT product_id, product_name, MONTH
FROM t1;

COMMIT;

SELECT * FROM t2 order by product_id, product_name, MONTH;

------------------------------
111 Apple 1
111 Apple 2
111 apple 3
111 Apple 4
222 oranges 1
222 oranges 2
222 oranges 3
222 oranges 4
333 bananas 1
333 bananas 2
333 bananas 3
333 bananas 4

12 rows have been selected.

Oracle-insert Performance Optimization

Data Tables cannot be inserted or updated in Oracle databases.

Data Tables cannot be inserted or updated in Oracle databases.

Oracle insert Extension

Oracle multi-table insert operations

2. Conditional insert all

 

Create table small_orders
(Order_id NUMBER (12) not null,
Customer_id NUMBER (6) not null,
Order_total NUMBER (8, 2 ),
Sale_rep_id NUMBER (6)

);

Create table medium_orders as select * FROM small_orders;

Create table large_orders as select * FROM small_orders;

Create table special_orders
(Order_id NUMBER (12) not null,
Customer_id NUMBER (6) not null,
Order_total NUMBER (8, 2 ),
Sale_rep_id NUMBER (6 ),
Credit_limit NUMBER (9, 2 ),
Cust_email VARCHAR2 (30)

);

INSERT ALL
Why order_total <100000 THEN
INTO small_orders
WHEN order_total> 100000 AND order_total <200000 THEN
INTO medium_orders
ELSE
INTO large_orders
SELECT order_id, customer_id, order_total, sales_rep_id
FROM orders;

For more details, please continue to read the highlights on the next page:

  • 1
  • 2
  • Next Page

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

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.