Click here to Skip to main content
11,721,671 members (67,676 online)
Rate this: bad
good
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
Edited 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 at 27-Oct-12 23:01pm
   
5'd
Lotus90 at 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
0 Sergey Alexandrovich Kryukov 435
1 OriginalGriff 269
2 Mika Wendelius 170
3 cyycoish 70
4 ppolymorphe 66
0 Sergey Alexandrovich Kryukov 2,249
1 OriginalGriff 1,024
2 F-ES Sitecore 690
3 Maciej Los 619
4 Richard MacCutchan 580


Advertise | Privacy | Mobile
Web03 | 2.8.150901.1 | Last Updated 27 Oct 2012
Copyright © CodeProject, 1999-2015
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