Syntax Error with my SQL Create Table As

Question

I am trying to create a new table in Microsoft SQL Server Management Studio based on two existing tables.

When I execute the query below, I get an error saying that there is an:

Incorrect syntax near the keyword 'SELECT'.

SQL code:

CREATE TABLE NEW_TABLE AS
 SELECT OLD_TABLE.A
    , OLD_TABLE.B
    , OTHER_OLD_TABLE.C
 FROM OLD_TABLE
 INNER JOIN OTHER_OLD_TABLE
 ON OLD_TABLE.A = OTHER_OLD_TABLE.D;

I looked at various other problems, but could not find a solution to mine. Do you have any idea what could be wrong with the syntax?

Solution

Alternatively, you can use SELECT * INTO new_table statement just like this.

SELECT OLD_TABLE.A
, OLD_TABLE.B
, OTHER_OLD_TABLE.C INTO NEW_TABLE
FROM OLD_TABLE
INNER JOIN OTHER_OLD_TABLE
ON OLD_TABLE.A = OTHER_OLD_TABLE.D;

this statement will also create a new table as you required.