I want to do the following:
INSERT INTO TableA
Select * from TableB
Order by TableB.FieldA
How do I do this??!!
Printable View
I want to do the following:
INSERT INTO TableA
Select * from TableB
Order by TableB.FieldA
How do I do this??!!
You can't use order by in an insert statement:
[email protected]> 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
[email protected]> desc table_a
Name Null? Type
----------------------------------------- -------- ----------------------------
D VARCHAR2(20)
E DATE
F VARCHAR2(20)
[email protected]> drop table table_b;
Table dropped.
[email protected]> create table table_b as select * from table_a order by d;
Table created.
[email protected]> 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.
[email protected]> 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.
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.