Unable to use ORDER BY in an INSERT statement

From: Dave Lumby <dlumby_at_axion.bt.co.uk>
Date: 1996/02/03
Message-ID: <4f00tn$s1t_at_pheidippides.axion.bt.co.uk>#1/1


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.

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 ...

At present the only route I can thing of that will work as required is to run the SELECT ... ORDER BY ... statement and then itterate through the result issuing succesive statements like -

INSERT INTO new_table (user_ref, start, rec_id) VALUES ('654321', '07:30', 1); INSERT INTO new_table (user_ref, start, rec_id) VALUES ('400000', '07:30', 2); etc

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.uk

"I don't mind valid criticism, as long as it doesn't come from other people"
Received on Sat Feb 03 1996 - 00:00:00 CET

Original text of this message