Re: select into question

Charles,
Using your table names and columns, this is the exact syntax that will select specific data from your tables (product and cart_item) and insert them into another table(order_item).

insert into order_item(order_id,product_id,name,date_created,quantity,price) select nextOrder_id ,c.product_id,p.name,SYSDATE,c.quantity, p.price from product p, cart_item c
where p.product_id = c.product_id
and c.cart_id = 66;

Marty

Charles L Wilson wrote in message
<7ub1bm$21d0$1_at_newssvr03-int.news.prodigy.com>...
>I need to insert data that I get from a query (in one step!)
>without a select into, it has to be done in 2 steps for each row(there
could
>be hundreds of rows)
>
>FIRST
>select nextOrder_id ,c.product_id,p.name,SYSDATE,c.quantity, p.price
>from product p, cart_item c
>where p.product_id = c.product_id
>and c.cart_id = 66
>
>I NEED TO STORE ALL ROWS RETURNED IN VARIABLES, AND THEN
>
>insert into order_item(order_id,product_id,
>name,date_created,quantity,price)
>values
>HERE I NEED TO TAKE OUT VALUE OF VARIABLES AND PUT INTO MY VALUES CLAUSE
>THIS NEEDS TO BE REPEATED FOR EACH ROW.
>
>This is not a practical solution.
>I need to be told how to do a select into statement. I know it is an oracle
>sql statement.
>can someone tell me how to structure a select into statement????
>
>
>
>>> I need to know how to select into a table from a table join. All tables
>are
>>> in the same table space.
>>>
>>> tables
>>> order_item: the one to be filled with info
>>> cart_item: contains contents of shopping cart
>>> product: extra info about each item
>>>
>>> some thing like
>>>
>>> select into order_item(order_id,product_id,
>>> name,date_created,quantity,price)
>>> values
>>> ( select nextOrder_id ,c.product_id,p.name,SYSDATE,c.quantity, p.price
>>> from product p, cart_item c
>>> where p.product_id = c.product_id
>>> and c.cart_id = cart_id)
>>>
>>> I need some sort of example to copy
>>>
>>> All help appreciated
>>>
>>> Charles
>>
>>Hi, try following
>>
>>INSERT table
>>SELECT * FROM other_table
>>
>>Just put the 'insert table' before your datasource.
>>
>>Milo
>>
>
>
Received on Sat Oct 16 1999 - 21:42:22 CDT