Why Does 'SELECT * INTO new_table FROM old_table;' Fail in Oracle and How Can I Correctly Create a New Table?

ELECT INTO Unexpected Mischief in Oracle

In the realm of SQL, the SELECT INTO statement holds a prominent position, enabling effortless data extraction and insertion into a new destination. However, in the context of Oracle's unique dialect, this operation unveils a surprising twist.

Contrary to conventional expectations, the query "SELECT * INTO new_table FROM old_table;" triggers an unforeseen error: "SQL Error: ORA-00905: missing keyword." This anomaly stems from Oracle's distinct interpretation of the INTO clause.

Unveiling Oracle's Implementation

Oracle deviates from the standard SELECT INTO behavior, opting for an alternative approach. If the target table already exists, Oracle executes an implicit INSERT INTO operation, adhering to the following syntax:

insert into new_table
select * from old_table
/

Alternatively, to create a brand-new table based on existing records, Oracle employs the following syntax:

create table new_table as
select * from old_table
/

Intriguingly, Oracle also offers a solution for creating an empty table:

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

By appending a WHERE clause with an inherently false condition, Oracle ingeniously constructs an empty table without copying data from the source.

Oracle's distinctive approach to SELECT INTO presents a departure from the established norm, compelling developers to adapt to its unique syntax and semantics. However, despite the initial surprise, this deviation offers flexibility and control over data manipulation within Oracle's vast database ecosystem.

The above is the detailed content of Why Does 'SELECT * INTO new_table FROM old_table;' Fail in Oracle and How Can I Correctly Create a New Table?. For more information, please follow other related articles on the PHP Chinese website!