Whyever would you want data inserted in order? There is no guarantee that
Oracle will actually store the records "in order", there is no performance
gain, and you can always retrieve the records in order by using an order by
statement -- if you really need ordered data, you could use a
index-organized table with all of your columns, with the date as the first
column. But methinks this would be dangerous for a heavy transaction
table. (Gurus, please correct me if I'm wrong here)
However, if you are still keen, you could do this through a PL/SQL block,
something like the following:
declare
cursor get_data is
select col1, col2, col3, ...
from unordered_table
order by whatever;
begin
for dataRec in get_data loop
insert into ordered_table (col1, col2, col3, ...)
values (dataRec.col1, dataRec.col2, dataRec.col3, ...)
end loop;
end;
/
Cheers!
Diana
"Browett, Darren"
<[EMAIL PROTECTED] To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
lam.bc.ca> cc:
Sent by: Fax to:
[EMAIL PROTECTED] Subject: Creating a sorted table
05/25/2001 06:45 PM
Please respond to
ORACLE-L
We have un-ordered data in a table that needs to be inserted into a
transaction table in
order of the date that the transaction took place.
Oracle does not allow "INSERT ..... AS SELECT ..... ORDER BY....."
or "CREATE TMP_TABLE ..... AS SELECT ..... ORDER BY......"
Is there a method by which I can accomplish this.
Thank you in advance
Darren Browett
Sys Admin
City of Coquitlam
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Browett, Darren
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
