Click here to Skip to main content
12,452,066 members (25,399 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL-Server T-SQL
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 27-Oct-12 10:01am
Lotus90317
Updated 27-Oct-12 10:04am
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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:
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:
insert into students(name) values('John');
...and the server will add the next available value to the [id] field.
  Permalink  
Comments
Kuthuparakkal 27-Oct-12 23:01pm
   
5'd
Lotus90 30-Oct-12 9:20am
   
Thank u ..It's working well !
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Quote:
CREATE TABLE Persons
(
P_Id int PRIMARY KEY IDENTITY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160826.1 | Last Updated 27 Oct 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100