How to insert data in Identity Column?

Ask Question

Home > DeveloperSection > Forums > How to insert data in Identity Column?

Amit Singh

Total Post:103

Points:721

Re: How to insert data in Identity Column?

Posted on November-12-2010 9:01 AM

Hi Haider,

Your query is correct, but the problem is that you can not insert data directly into Identity column.

All you have to do is to set Identity insert of that column on, and after you are done just set it off.

Here is the script that will do what you want.

SET IDENTITY_INSERT tblLogin ON

INSERT into tblLogin ([ID], [FirstName], [LastName], [Password]) VALUES (15, N'abc', N'xyz', N'pass')

SET IDENTITY_INSERT tblLogin OFF

Hope this will help you.

report abuse

Shankar M

Total Post:25

Points:175

Re: How to insert data in Identity Column?

Posted on February-10-2013 9:12 PM

Hi Rizvi,

There is no problem with the query, The problem is that you cannot insert into a column that is defined as Identity column in SQL

Server.

Instead, you have to Modify the column Property Identity Specification - (Is Identity) to No. Then, your statement will work fine.

To insert into a table EMP with Identity column where its Is Identity Property set to Yes can be done as shown.

CREATE TABLE EMPLOYEE(EID INT IDENTITY(1,1),

ENAME VARCHAR(20));

INSERT INTO EMPLOYEE(ENAME)VALUES('SAM');

Here, the Identity value is managed the SQL Server and we don't need to specify

the column while writing your Insert Statement.

Regards,

Shankar

Modified On Feb-10-2013 09:14:48 PM

report abuse

Follow MindStick

Copyright © 2010 - 2015 MindStick Software Pvt. Ltd. All Rights Reserved.