How Does Oracle's `SELECT INTO` Differ from Standard SQL, and What are the Workarounds?

Oracle's Unique Interpretation of SELECT INTO

While the standard SQL syntax for SELECT INTO involves creating a new table and populating it with data from an existing table, Oracle's implementation differs significantly.

Error Encountered:

When attempting to execute the standard SELECT * INTO new_table FROM old_table in Oracle, you may encounter the "missing keyword" error (ORA-00905). This is because Oracle's syntax for performing the same operation requires a different approach.

Oracle's Syntax for Creating a New Table and Populating Data:

To successfully execute SELECT INTO in Oracle, you have several options:

1. If the new table already exists:

   Use the INSERT INTO statement:

   INSERT INTO new_table
   SELECT *
   FROM old_table;

2. If you want to create a new table based on the records in an existing table:

   Use the CREATE TABLE ... AS SELECT statement:

   CREATE TABLE new_table AS
   SELECT *
   FROM old_table;

3. If the purpose is to create a new but empty table:

   Add a WHERE clause with a condition that can never be true:

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

Important Considerations:

• The CREATE TABLE ... AS SELECT statement only creates a table with the same projection as the source table.
• The new table will not have any constraints, triggers, or indexes inherited from the original table.

The above is the detailed content of How Does Oracle's `SELECT INTO` Differ from Standard SQL, and What are the Workarounds?. For more information, please follow other related articles on the PHP Chinese website!