INSERT Into with Order by

[kbissing]

I want to do the following:
INSERT INTO TableA
Select * from TableB
Order by TableB.FieldA

How do I do this??!!

[marist89]

You can't use order by in an insert statement:
jeff@dev815nt.us> insert into table_b select * from table_a
2 order by 1
3 /
order by 1
*
ERROR at line 2:
ORA-00933: SQL command not properly ended


jeff@dev815nt.us> desc table_a
Name Null? Type
----------------------------------------- -------- ----------------------------
D VARCHAR2(20)
E DATE
F VARCHAR2(20)

jeff@dev815nt.us> drop table table_b;

Table dropped.

jeff@dev815nt.us> create table table_b as select * from table_a order by d;

Table created.

jeff@dev815nt.us> select * from table_b;

D E F
-------------------- --------- --------------------
3607551298 30-JAN-50 3111188
3607551428 30-JAN-50 2160118
3607551428 30-JAN-50 2160118
3607551428 30-JAN-50 2160118
3607551484 30-JAN-50 3934798
3607551484 30-JAN-50 3934798
3607551484 30-JAN-50 3934798

7 rows selected.

jeff@dev815nt.us> select * from table_a
2 /

D E F
-------------------- --------- --------------------
3607551428 30-JAN-50 2160118
3607551484 30-JAN-50 3934798
3607551428 30-JAN-50 2160118
3607551484 30-JAN-50 3934798
3607551298 30-JAN-50 3111188
3607551428 30-JAN-50 2160118
3607551484 30-JAN-50 3934798

7 rows selected.

[akkerend]

In 8.1.6 you can use ORDER BY in (inline) views, and
INSERT INTO tab_a
SELECT * FROM tab_b
ORDER BY col_c
will work.

But why do you want such an insert? You cannot be sure that if you try
SELECT * FROM tab_a
it will be ordered on col_c.