For advanced usage of INSERT statements in Oracle, the Insert all statement describes:
1. Insert all inserts unconditionally
CREATE TABLE T1 (product_id number, product_name VARCHAR2, MONTH number), insert into T1 VALUES (111, ' Apple ', 1); INSERT INTO T1 VALUES (222, ' oranges ', 1); INSERT into T1 values (333, ' bananas ', 1); COMMIT; CREATE TABLE T2 as SELECT * from T1 WHERE 1=2;insert all to 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, Monthfrom T1; COMMIT; SELECT * from T2 ORDER by product_id, Product_Name, MONTH;
---------- ---------- ----------
111 Apples 1
111 Apples 2
111 Apples 3
111 Apples 4
222 Oranges 1
222 Oranges 2
222 Oranges 3
222 Oranges 4
333 Banana 1
333 Banana 2
333 Banana 3
333 Banana 4
12 rows have been selected.
2, conditional insert All
CREATE TABLE small_orders (order_id number () not NULL, customer_id number (6) is 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 (a) is not NULL, customer_id number (6) is not NULL, Order_ Total number ( 8,2), sale_rep_id number (6), Credit_limit number (9,2), Cust_email VARCHAR2 (()); Nsert All to Order_total < 100000 then to small_orders when order_total > 100000 and Order_total < 200000 then to medium_orders ELSE into large_orders SELECT order_id, customer_id, Order_ Total, sales_rep_id from orders;
3. Conditional Insert First
If the value of the first when clause is the TRUE,ORACLE server executes the appropriate into clause for the given row,
And skips the later 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).
INSERT first when Ottl < 100000 to small_orders VALUES (OID, Ottl, sid, Cid) when Ottl > 10000 0 and Ottl < 200000 then to medium_orders VALUES (OID, Ottl, sid, Cid) when ottl > 290000 then into Special_orders when ottl > 200000 then into large_orders VALUES (OID, Ottl, sid, Cid) SELECT o.order_id OID, o.customer_id CID, O.order_total ottl, o.sales_rep_id SID, C.credit_limit cl, C.cust_email CEM From orders O, customers c WHERE o.customer_id = c.customer_id; SELECT * from Small_orders; SELECT * from Medium_orders; SELECT * from Large_orders; SELECT * from Special_orders;
---------------------------------
by Dylan.