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?

SQL Server's syntax is `SELECT ... INTO new table FROM....` Your example is representative of MySQL's syntax. — Michael Berkowski, Apr 20 '15 at 17:28
Thank you, I did not realise. Apologies for the duplicate post - definitely was a result of my confusion. — Smile5.09, Apr 21 '15 at 08:35
No need to apologize . The community is here to point you to good answers. They may already exist. — Michael Berkowski, Apr 21 '15 at 10:43

score 8 · Accepted Answer · answered Apr 20 '15 at 17:27

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.

Syntax Error with my SQL Create Table As

1 Answers1