Creating new table with SELECT INTO in SQL

sql oracle select create-table
Aspirant picture Aspirant · Aug 9, 2012 · Viewed 183.9k times · Source

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.

Answer

Justin Cave picture Justin Cave · Aug 9, 2012

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.

Related questions

DATEDIFF function in Oracle

I need to use Oracle but DATEDIFF function doesn't work in Oracle DB. How to write the following code in Oracle? I saw some examples using INTERVAL or TRUNC. SELECT DATEDIFF ('2000-01-01','2000-01-02') AS …

Read More
If statement in select (ORACLE)

Hi I have simply select and works great: select 'CARAT Issue Open' issue_comment, i.issue_id, i.issue_status, i.issue_title, i.ISSUE_summary ,i.issue_description, i.severity, gcrs.Area_name, gcrs.sector_name, substr(gcrs.stream_…

Read More
How to kill a running SELECT statement

How can I stop a running SELECT statement by killing the session? The command is continuously giving me output based on the SELECT statement, I want to stop it in between.

Read More