CODEWITHSUNDEEP
sql-servert-sql
Lidbey
Lidbey

Reputation: 371

MS SQL insert into auto increment column default value

I am creating a database interface to make some basic operations on tables.

CREATE TABLE products (
    ID int IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(20)
)

As long as I have been using MySQL database, it worked correctly to insert DEFAULT value onto 'ID' column

INSERT INTO products (ID, Name) VALUES (DEFAULT, "ProductName")

But MS SQL won't let me do that, the error I get is DEFAULT or NULL are not allowed as explicit identity values..

I have been looking for solutions and couldn't find one - is there a way to include ID in the list of columns in the insert statement, but actually let the database handle the value to be inserted(auto-increment value)?

The answer to just not include ID in the list of columns is not solving my problem, as I access the database from a C++ program and try to do it as abstract as possible (and some different tables don't have auto-increment on ID)

Upvotes: 1

Views: 3428

Answers (2)

SQLpro
SQLpro

Reputation: 5187

Two solution :

  1. ignore the ID column in the column list :

    INSERT INTO products (Name) VALUES ('ProductName');

  2. do not specify the column list if you want to insert in all columns except identity :

    INSERT INTO products VALUES (DEFAULT, ProductName);

By the way, double quote must never be used for strings but only simple quote. Double quote is reserved for "abnormal" objest names...

Upvotes: 1

Antonio Calo
Antonio Calo

Reputation: 53

You must be define the Id column like this:

[id] [int] IDENTITY(1,1) NOT NULL

Upvotes: 0

Related Questions