ORDER BY not allowed in INSERT..SELECT ?
376537Aug 20 2003 — edited Aug 22 2003I'm trying to INSERT..SELECT into a table, the SELECT should be ordered by a clause, but Oracle (8.1.7) does not permit that.
INSERT INTO SumTable
SELECT Field1,Field2
FROM FullTable
WHERE field1 = 1
ORDER BY field2;
I get: ORA-00933: SQL command not properly ended
It doesn't matter if the "order by" field is part of the SELECT or not. When I remove the ORDER BY the INSERT works perfectly. Oracle docs do not mention this limitation. Is this known issue? Can I make it to work?
I do have a solution in PL/SQL, open a cursor for the SELECT with the ORDER BY, INSERT one row at a time in the cursor loop, but its too slow when I have more than 10K records to insert.
Thanks,
Yoram Ayalon