Getting an "Incorrect syntax near '(' " error message when using the standard create table using a select in SQL Server

Question

Why am I getting the error in the Title of this post when I run the following query using SQL Server via Microsoft SQL Server Management Studio?

-- create the dash_airports table
CREATE TABLE dash_airports 
    AS ( SELECT [Loc_Id], [ARP_Latitude], [ARP_Longitude], [Facility_Type], [Wind_Indicator] 
    FROM airportandrunway.dbo.Airports
    WHERE  [Loc_Id] IN ('09J','14A','18A','1V6','22S','2I0','YKM','YKN') );

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '('.

I followed the instructions for Solution 2 in this how-to-article, but it clearly didn't work. So, as a sanity check, I ran it in MySQL Workbench and it did work there! What gives?

Proof that it worked in Workbench:

Answer 1

Congratulations! You found SQL dialects.

There are many SQL database systems, and each system has its own SQL dialect. Sometimes the difference is small, sometimes it is big. Usually, the difference appears in DDL (Data Definition Language) part of the SQL which has command to manipulate database objects, like CREATE TABLE, ALTER INDEX and similar.

The article you referred is a bad article, because it does not state which dialect it teaches. It is actually MySQL, but there is no mentioning of it in the article.

The MySQL dialect is named after the MySQL DBMS, which uses it - and of course, the 'Workbench' tool connected to MySQL DBMS worked perfectly.

-- That is MySQL
CREATE TABLE gamer
AS 
SELECT
  gamer, score, championship_date
FROM championship
WHERE championship_date <= 2020-08-10;

Microsoft SQL Server uses T-SQL. Analogous to the MySQL statement in T-SQL would be:

-- That is T-SQL
SELECT
  gamer, score, championship_date
INTO gamer
FROM championship
WHERE championship_date <= 2020-08-10;

By the way, the MySQL dialect is very close to PL/SQL used by Oracle. There are differences, but create table from select is done similarly.

Majority of other dialects, for this task, are mimicking other oldest dialect - Transact SQL, developed by Sybase. The T-SQL is a very close successor of Transact SQL, but there are also differences.

Answer 2

As Denis mentioned, MSSQL (except for Synapse Analytics) doesn't have CREATE TABLE AS SELECT. The counterpart in MSSQL is what we refer to as SELECT INTO i.e., the ability to create and populate a table based on the result from a SELECT statement.

Example:

SELECT Loc_Id, ARP_Latitude, ARP_Longitude, Facility_Type, Wind_Indicator 
INTO dash_airports
FROM airportandrunway.dbo.Airports
WHERE  Loc_Id IN ('09J','14A','18A','1V6','22S','2I0','YKM','YKN')