Search code examples
sqloracle-databaseselectcreate-table

Creating new table with SELECT INTO in SQL

Possible Duplicate:
SELECT INTO using Oracle

I have came across SQL SELECT INTO statement for creating new table and also dumping old table records into new table in single SQL statement as

  SELECT * INTO NEW_TABLE FROM OLD_TABLE;

But when i am trying the above query it is giving error as ORA-00905: missing keyword

Is it possible to do that in ORACLE ?

Thanks.

Solution

  • The syntax for creating a new table is

    CREATE TABLE new_table
AS
SELECT *
  FROM old_table

    This will create a new table named new_table with whatever columns are in old_table and copy the data over. It will not replicate the constraints on the table, it won't replicate the storage attributes, and it won't replicate any triggers defined on the table.

    SELECT INTO is used in PL/SQL when you want to fetch data from a table into a local variable in your PL/SQL block.