40

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.

Community
  • 1
  • 1
Aspirant
  • 2,238
  • 9
  • 31
  • 43

1 Answers1

90

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.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • 5
    To complete the picture: `create table as ..` is standard SQL and supported by nearly all DBMS. Even some of those that use SELECT INTO to create new tables. –  Aug 09 '12 at 21:41
  • Doesn't work in SQL Server Management Studio. – Doug Hauf May 02 '14 at 14:17