Click here to Skip to main content
15,867,321 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi

I have database table where the id should increment automatically.
I set the "identity specification to yes and increment it by 1 "
also I put the "SET IDENTITY_INSERT MyTable ON " but I still have the error.
"Incorrect syntax near the word [table name]"

How I Can insert to the table and the make id increment automatically every time of insertion ?
Posted
Updated 25-Apr-22 19:42pm
v2

SET IDENTITY_INSERT ON(http://msdn.microsoft.com/en-us/library/aa259221(v=sql.80).aspx[^]) allows explicit values to be inserted into the identity column of a table.
If it is OFF, you can not issue INSERT statements, that affect an identity filed.

If you want an identity filed to work on it's own, just omit that filed from the insert statement.
Let's suppose, you have this schema:
SQL
CREATE TABLE [dbo].[students](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_students] PRIMARY KEY ([id] ASC)
)

Than you can use this DML statement:
SQL
insert into students(name) values('John');

...and the server will add the next available value to the [id] field.
 
Share this answer
 
Comments
Kuthuparakkal 27-Oct-12 23:01pm    
5'd
Lotus90 30-Oct-12 9:20am    
Thank u ..It's working well !
Quote:
CREATE TABLE Persons
(
P_Id int PRIMARY KEY IDENTITY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900