I get the error incorrect syntax near ... in Microsoft SQL Server Management Studio

Question

I am trying to write this SQL code in Microsoft SQL Server Management Studio:

create table Countries
(
    Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(50) NOT NULL
);

and the problem is that I get this error on the comma:

Incorrect syntax near ','. Expecting ID, QUOTED_ID, STRING or TEXT_LEX.

and error in the 50:

Incorrect syntax near '50'. Expecting '(', or Select.

Just saying that I am beginner and I am not sure what is the error.

Please refer https://stackoverflow.com/questions/15049434/sql-create-statement-incorrect-syntax-near-auto-increment — Nayan, Jan 31 '21 at 11:17
"I am beginner" is not an excuse to avoid looking up and learning basic syntax. — SMor, Jan 31 '21 at 12:36
This is the official SQL Server [CREATE TABLE documentation reference](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver15). Although syntax is similar to other ISO SQL database products, each has extensions not covered by the standard. Always consult the vendor documentation as the authoritative source. — Dan Guzman, Jan 31 '21 at 12:48

score 0 · Accepted Answer · answered Jan 31 '21 at 11:20

0

If you are using MySQL the following is the syntax:

create table Countries
(
    Id INT NOT NULL AUTO_INCREMENT,
    Name VARCHAR(50) NOT NULL,
    PRIMARY KEY (Id)
);

If you are using MSSQL the following is the syntax:

create table Countries
(
    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(50) NOT NULL
);

answered Jan 31 '21 at 11:20

Neo

Thanks that helped I was not sure because I was working with MySQL and I have to use MSSQL now – PawchowexDD Jan 31 '21 at 16:21

score 0 · Answer 2 · answered Jan 31 '21 at 11:20

0

The AUTO_INCREMENT keyword is used in MySQL.
SQL Server uses IDENTITY to perform the auto-increment feature.

So your T-SQL code should be:

CREATE TABLE Countries (
    Id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    Name varchar(50) NOT NULL
);

answered Jan 31 '21 at 11:20

2 Answers2