Re: select into question

Charles L Wilson wrote:
> 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????

In Sybase ASE, variables cannot be structured into arrays. Hence, copying into variables the values of many rows is almost impossible.

Usually, temp tables are used to simulate arrays and matrices. What about this?: Fill a new temp table with:

 select ... into #mymatrix from table1, table2 where ...

Then access data from that temp table as you designed to do with variables. Finally, insert into the permanent table:

 insert into perm_table select ... from #mymatrix

The select...into and insert...select statements become a two single SQL-statement, each handling many rows. That's good for the performance of the stored proc or isql batch.

Regards,
Mariano Corral

• Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!