Re: Unable to use ORDER BY in an INSERT statement

I believe Dr. Codd & Chris Date could write a book on this one but in a nutshell, the relational model does not allow for the significance of order in the storing of records, only when they are retrieved!

Matter of fact, they have written quite a few books on this subject.

Dave Lumby (dlumby_at_axion.bt.co.uk) 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.
:
: 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 Fri Feb 09 1996 - 00:00:00 CET