Re: Unable to use ORDER BY in an INSERT statement
Date: 1996/02/05
Message-ID: <4f4dg5$42s_at_pheidippides.axion.bt.co.uk>#1/1
In article <4f00tn$s1t_at_pheidippides.axion.bt.co.uk>,
dlumby_at_axion.bt.co.uk (Dave Lumby) wrote:
>Folks,
> I'm trying to transfer fields from one table to another in a specific
>order. Ideally I'd like to be able to use a statement something like -
>
>CREATE SEQUENCE seq;
>
>INSERT INTO new_table
> SELECT account_no, '07:30', seq.nextval FROM big_table
> WHERE filter = 1 ORDER BY balance, zip_code;
>
>DROP SEQUENCE seq;
>
>However, INSERT INTO TABLE <sub-query> doesn't permit the sub-query to
>contain an ORDER BY clause.
<SNIP>
>
You could try spooling the output to a file, then use SQL*Loader.
e.g.
CREATE SEQUENCE seq;
SPOOL file.dat
SELECT account_no, '07:30', seq.nextval from big_table
WHERE fileter=1 ORDER BY BALANCE, zip code
SPOOL OFF
then
SQL*LOAD the resulting outoput
Dave Eason, LSEC, Apsley
-- I don't know what I'm doing here, I'll carry on, regardless (The Beautiful South) BT might not necessarily agree with what I say - I seldom do!Received on Mon Feb 05 1996 - 00:00:00 CET