8

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: enter image description here

0

2 Answers 2

14

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.

3
  • Most of my experience, both through projects in my masters in data analytics engineering program, and in Udemy courses and tutorials have been with MySQL. I am aware of the differences between the types, my mistake was that I Google searched "How to temporarily save the results of a query in sql server during the session" when I found it, so I didn't even check to see if it said which sub type it was using in it since I assumed it would be T-SQL. The quality of my Google results has gone down a lot over the past few years to be candid with you.
    – Marlen
    Apr 13, 2023 at 16:00
  • 2
    "because it does not state which dialect it teaches." - CREATE TABLE AS is specified in the SQL standard, so it's not really a "dialect thing". And MySQL's procedural language is from being close to Oracle's PL/SQL
    – user1822
    Apr 13, 2023 at 18:36
  • 1
    The OP does already know something about SQL dialects, since they figured out to use [] as the identifier quoting character (MySQL uses backticks).
    – Barmar
    Apr 14, 2023 at 14:34
7

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')
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.