sql - Oracle Insert Select with order by

Question

I am working on a plsql procedure where i am using an insert-select statement. I need to insert into the table in ordered manner. but the order by i used in the select sql is not working.

is there any specific way in oracle to insert rows in orderly fashion?

Answer 1

The use of an ORDER BY within an INSERT SELECT is not pointless as long as it can change the content of the inserted data, i.e. with a sequence NEXTVAL included in the SELECT clause. And this even if the inserted rows won't be sorted when fetched - that's the role of your ORDER BY clause in your SELECT clause when accessing the rows.

For such a goal, you can use a work-around placing your ORDER BY clause in a sub-query, and it works:

INSERT INTO myTargetTable
(
  SELECT mySequence.nextval, sq.* FROM
    (   SELECT f1, f2, f3, ...fx 
          FROM mySourceTable
         WHERE myCondition
      ORDER BY mySortClause
    ) sq
)