How to Correctly Use 'SELECT INTO' in Oracle to Avoid ORA-00905 Errors?

Troubleshooting "SELECT INTO" Error in Oracle

The "SELECT INTO" statement in Oracle, which inserts the results of a SELECT query into a new or existing table, can result in an ORA-00905 "missing keyword" error. This error message indicates a discrepancy between the Oracle implementation and the expected standard behavior.

Oracle Implementation:

Unlike the standard implementation, Oracle requires a different syntax for "SELECT INTO":

• If the target table already exists:
  Use the "INSERT INTO ... SELECT" syntax:

  INSERT INTO new_table SELECT * FROM old_table;

• If the target table doesn't exist and you want to create it:
  Use the "CREATE TABLE ... AS SELECT" syntax:

  CREATE TABLE new_table AS SELECT * FROM old_table;

Other Considerations:

If the target table exists and you want to create an empty duplicate, use the following syntax:

CREATE TABLE new_table AS SELECT * FROM old_table WHERE 1 = 2;

Remember that the "CREATE TABLE ... AS SELECT" method only creates a table with the same column projection as the source table. To add constraints, triggers, or indexes, you must manually create them separately.

The above is the detailed content of How to Correctly Use 'SELECT INTO' in Oracle to Avoid ORA-00905 Errors?. For more information, please follow other related articles on the PHP Chinese website!