How to Correctly Use SELECT INTO in Oracle to Create or Populate Tables?

SELECT INTO in Oracle

When attempting to perform a SELECT INTO operation in Oracle using the query SELECT * INTO new_table FROM old_table, you may encounter the error missing keyword (ORA-00905). Oracle handles SELECT INTO queries differently from the standard implementation.

Handling Existing Tables

If the new_table already exists, you can perform an insert operation using:

insert into new_table 
select * from old_table

Creating New Tables

To create a new table based on records from an old table:

create table new_table as 
select * from old_table

Creating Empty Tables

For creating a new, empty table, use a WHERE clause with a condition that can never be true:

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

Additional Considerations

Note that the CREATE TABLE ... AS SELECT syntax creates a table with the same projection as the source table. Constraints, triggers, and indexes from the original table are not included and must be manually added if necessary.

The above is the detailed content of How to Correctly Use SELECT INTO in Oracle to Create or Populate Tables?. For more information, please follow other related articles on the PHP Chinese website!