Multitable INSERT SQL can insert simultaneously into more than one tables. You can also specify optional conditions which determine which values are inserted into which tables.
SELECT * FROM sales_input_table; PRODUCT_ID CUSTOMER_ID WEEKLY_ST SALES_SUN SALES_MON SALES_TUE SALES_WED SALES_THU SALES_FRI SALES_SAT ---------- ----------- --------- ---------- ---------- ---------- -------------------- ---------- ---------- 111 222 01-OCT-00 100 200 300 400 500 600 700 222 333 08-OCT-00 200 300 400 500 600 700 800 333 444 15-OCT-00 300 400 500 600 700 800 900
The multitable insert statement looks like this:
INSERT ALL INTO sales (prod_id, cust_id, time_id, amount) VALUES (product_id, customer_id, weekly_start_date, sales_sun) INTO sales (prod_id, cust_id, time_id, amount) VALUES (product_id, customer_id, weekly_start_date+1, sales_mon) INTO sales (prod_id, cust_id, time_id, amount) VALUES (product_id, customer_id, weekly_start_date+2, sales_tue) INTO sales (prod_id, cust_id, time_id, amount) VALUES (product_id, customer_id, weekly_start_date+3, sales_wed) INTO sales (prod_id, cust_id, time_id, amount) VALUES (product_id, customer_id, weekly_start_date+4, sales_thu) INTO sales (prod_id, cust_id, time_id, amount) VALUES (product_id, customer_id, weekly_start_date+5, sales_fri) INTO sales (prod_id, cust_id, time_id, amount) VALUES (product_id, customer_id, weekly_start_date+6, sales_sat) SELECT product_id, customer_id, weekly_start_date, sales_sun, sales_mon, sales_tue, sales_wed, sales_thu, sales_fri, sales_sat FROM sales_input_table;
Assuming these are the only rows in the sales table, the contents now look like this:
SELECT * FROM sales ORDER BY prod_id, cust_id, time_id; PROD_ID CUST_ID TIME_ID C PROMO_ID QUANTITY_SOLD AMOUNT COST ---------- ---------- --------- - ---------- ------------- ---------- ---------- 111 222 01-OCT-00 100 111 222 02-OCT-00 200 111 222 03-OCT-00 300 111 222 04-OCT-00 400 111 222 05-OCT-00 500 111 222 06-OCT-00 600 111 222 07-OCT-00 700 222 333 08-OCT-00 200 222 333 09-OCT-00 300 222 333 10-OCT-00 400 222 333 11-OCT-00 500 222 333 12-OCT-00 600 222 333 13-OCT-00 700 222 333 14-OCT-00 800 333 444 15-OCT-00 300 333 444 16-OCT-00 400 333 444 17-OCT-00 500 333 444 18-OCT-00 600 333 444 19-OCT-00 700 333 444 20-OCT-00 800 333 444 21-OCT-00 900
The next examples insert into multiple tables. Suppose you want to provide to sales representatives some information on orders of various sizes. The following example creates tables for small, medium, large, and special orders and populates those tables with data from the sample table oe.orders:
CREATE TABLE small_orders
(order_id NUMBER(12) NOT NULL,
customer_id NUMBER(6) NOT NULL,
order_total NUMBER(8,2),
sales_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),
sales_rep_id NUMBER(6),
credit_limit NUMBER(9,2),
cust_email VARCHAR2(30)
);
The first multitable insert populates only the tables for small, medium, and large orders:
INSERT ALL WHEN order_total < 1000000 THEN INTO small_orders WHEN order_total > 1000000 AND order_total < 2000000 THEN INTO medium_orders WHEN order_total > 2000000 THEN INTO large_orders SELECT order_id, order_total, sales_rep_id, customer_id FROM orders;
You can accomplish the same thing using the ELSE clause in place of the insert into the large_orders table:
INSERT ALL WHEN 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, order_total, sales_rep_id, customer_id FROM orders; <pre> The next example inserts INTO the small, medium, AND large TABLES, AS IN the preceding example, AND also puts orders greater than 2,900,000 INTO the special_orders TABLE. This TABLE also shows how TO USE COLUMN aliases TO simplify the statement: <pre lang="sql"> INSERT ALL WHEN ottl < 100000 THEN INTO small_orders VALUES(oid, ottl, sid, cid) WHEN ottl > 100000 AND ottl < 200000 THEN INTO medium_orders VALUES(oid, ottl, sid, cid) WHEN ottl > 200000 THEN INTO large_orders VALUES(oid, ottl, sid, cid) WHEN ottl > 290000 THEN INTO special_orders 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;
Finally, the next example uses the FIRST clause to put orders greater than 2,900,000 into the special_orders table and exclude those orders from the large_orders table:
INSERT FIRST WHEN ottl < 100000 THEN INTO small_orders VALUES(oid, ottl, sid, cid) WHEN ottl > 100000 AND ottl < 200000 THEN INTO 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;
Oracle Multi-table Insert Syntax
INSERT [hint]
{
ALL insert_into_clause [values_clause] [error_logging_clause]
[insert_into_clause [values_clause] [error_logging_clause]]...
|
conditional_insert_clause
}
conditional_insert_clause ::=
[ALL | FIRST]
WHEN condition
THEN
insert_into_clause
[ values_clause ]
[ error_logging_clause ]
[ insert_into_clause
[ values_clause ]
[ error_logging_clause ]
]...
[ WHEN condition
THEN
insert_into_clause
[ values_clause ]
[ error_logging_clause ]
[ insert_into_clause
[ values_clause ]
[ error_logging_clause ]
]...
]...
[ ELSE insert_into_clause
[ values_clause ]
[ error_logging_clause ]
[ insert_into_clause
[ values_clause ]
[ error_logging_clause ]
]...
]
insert_into_clause :=
INTO dml_table_expression_clause [ t_alias ]
[ (column [, column ]...) ]
values_clause :=
VALUES ({ expr | DEFAULT }
[, { expr | DEFAULT } ]...
)
Olga …
Otkuda material ? …
Филипп…
история 20 века…
Add A Comment
You must be logged in to post a comment.