SELECT INTO using Oracle

If NEW_TABLE already exists then …

insert into new_table 
select * from old_table
/

If you want to create NEW_TABLE based on the records in OLD_TABLE …

create table new_table as 
select * from old_table
/

If the purpose is to create a new but empty table then use a WHERE clause with a condition which can never be true:

create table new_table as 
select * from old_table
where 1 = 2
/

Remember that CREATE TABLE … AS SELECT creates only a table with the same projection as the source table. The new table does not have any constraints, triggers or indexes which the original table might have. Those still have to be added manually (if they are required).

Leave a Comment