Oracle Multi-Table Insert Syntax?

Question

I'm taking my first look at Oracle's multi-table insert (I'm fairly new to SQL overall), and I'm not quite understanding the purpose/need for the SELECT at the end of the statement.

With a single-table INSERT, it's my understanding that either the VALUES clause or a subquery are used, but not both. Can someone explain the significance of the SELECT clause at the end of this INSERT statement? I've looked online, but I haven't found a clear answer.

INSERT ALL
WHEN prod_category='B' THEN
INTO book_sales(prod_id,cust_id,qty_sold,amt_sold)
VALUES(product_id,customer_id,sale_qty,sale_price)
WHEN prod_category='V' THEN
INTO video_sales(prod_id,cust_id,qty_sold,amt_sold)
VALUES(product_id,customer_id,sale_qty,sale_price)
WHEN prod_category='A' THEN
INTO audio_sales(prod_id,cust_id,qty_sold,amt_sold)
VALUES(product_id,customer_id,sale_qty,sale_price)
SELECT prod_category ,product_id ,customer_id ,sale_qty, sale_price
FROM sales_detail;

read the select first: select x,y from table1. For each row, when x='Foo', insert into table2, when x='Bar', then insert into table3, etc ... — tbone
– tbone, Commented Feb 16, 2014 at 22:04

Rida BENHAMMANE · Accepted Answer · 2014-02-16 21:58:45Z

2

The select is used to determine the values of the variable prod_category used in WHEN prod_category='B' THEN

answered Feb 16, 2014 at 21:58

Rida BENHAMMANE

4,1291 gold badge16 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle Multi-Table Insert Syntax?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related