Re: Unable to use ORDER BY in an INSERT statement

From: Dave Eason <easondc_at_hpohp2.wgw.bt.co.uk>
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

Original text of this message