Introduction to Oracle insert all statements

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