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

HOW TO INSERT DATA IN IDENTITY COLUMN?

Alex R
Alex R

Total Post:40

Points:280
Posted by  Alex R
 3415  View(s)
Ratings:
Rate this:

Hi,
I want to insert record in database and I am able to insert using insert query, but the problem is that I am unable to insert data in Identity column.

Here is the Query which I am writing

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

 

Please tell me what is wrong in the query?

Thanks in advance.

  1. Amit Singh

    Post:103

    Points:721
    Re: How to insert data in Identity Column?

    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.

  1. Shankar M

    Post:25

    Points:175
    Re: How to insert data in Identity Column?

    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

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!

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