Unable to use ORDER BY in an INSERT statement
Date: 1996/02/03
Message-ID: <4f00tn$s1t_at_pheidippides.axion.bt.co.uk>#1/1
Folks,
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.
Im tried an alternative approach of using my client application (VB4) to build an INSERT statement something like -
INSERT INTO new_table
SELECT account_no, '07:30', seq.nextval FROM big_table WHERE filter = 1 AND account_no IN ('654321', '400000', '123456', ....
BUT account_no is the PRIMARY KEY of big_table and the insertions into new_table occur in ascending order of the primary key regardless of the order of the entries in the <account_no IN ('654321'> part of the sub-query.
I need to be able preserve the order of the records selected by an ORDER BY clause and ensure that the values returned from seq.nextval occur in the same order as the rows returned by SELECT ... ORDER BY ...
BUT I need to be able to insert up to 2000 records in one go, so while the above will undoubtably work as I require, it could take quite some time to run.
Any suggestions for alternative approaches would be very welcome.
Thanks for taking the time to read this and for any help to may be able to offer.
Dave
Dave Lumby, Systems Integration Centre, BT Labs, Ipswich, IP5 7RE, UK Phone: +44 1473 642613 SMTP: D.Lumby_at_axion.bt.co.uk Fax: +44 1473 643019 MS-Mail: LUMBYDJ_at_btlip01.bt.co.uk
"I don't mind valid criticism, as long as it doesn't come from other people"
-- Dave Lumby, Systems Integration Centre, BT Labs, Ipswich, IP5 7RE, UK Phone: +44 1473 642613 SMTP: D.Lumby_at_axion.bt.co.uk Fax: +44 1473 643019 MS-Mail: LUMBYDJ_at_btlip01.bt.co.ukReceived on Sat Feb 03 1996 - 00:00:00 CET
"I don't mind valid criticism, as long as it doesn't come from other people"