SQL: Create new table from old

Question

I am trying to create one new table with the selective columns from the existing table. I used the following query,

create table New 
AS (select UnitID,Institution,State,MSA from Old);

But I am getting the error as,

Incorrect syntax near the keyword 'as'.

If I remove those ()-brackets it is saying ()-brackets are expected near AS.

Any suggestion would be helpful!

Possible duplicate of [How do I create a table based on another table](http://stackoverflow.com/questions/18254104/how-do-i-create-a-table-based-on-another-table) — Jens, Sep 27 '16 at 07:40
Both `New` and `Old` are reserved words, need to be delimited as `"Old"` and `"New"`. — jarlh, Sep 27 '16 at 07:45
@a_horse_with_no_name, both old and new have been reserved words since ISO/ANSI SQL-1999. — jarlh, Sep 27 '16 at 08:03
Just for your information: the way you try to this SQL expects the definitions for the columns of table to create between the brackets, not a SQL statement reading data from another table. So this is just a syntax error. The answer bellow (with SELECT...INTO) will do the trick. This works, but the preferred way would be to create a new table and copy the data into it. This will give you much more control over what happens. — Tom, Sep 27 '16 at 08:56
@Tom: that depends on the DBMS. That syntax **is** valid (and standard SQL). It the DBMS is Oracle this will probably fail because `old` and `new` are reserved keywords in Oracle (as jarlh mentioned) and can't be used as an identifier. — , Sep 27 '16 at 09:44

score 1 · Accepted Answer · answered Sep 27 '16 at 09:07

The basic syntax for creating a new table from old table is as follows:

CREATE TABLE NEW_TABLE_NAME AS
SELECT [ column1, column2...columnN ]
FROM OLD_TABLE_NAME
[ WHERE ]

Consider EMPLOYEE_DETAILS as new table name and EMPLOYEE as old table name

example code:

CREATE TABLE EMPLOYEE_DETAILS AS
 SELECT ID, SALARY
   FROM EMPLOYEE;

1 Answers1