Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • SQL Server : How to insert values in identity column programmatically?

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 383
    Comment on it

    Identity field is typically used as a primary key in database. When a new record is inserted into a table, this field automatically assigns an incremented value to this column.However, inserting our own value into this column is not straightforward.In this article,we will learn how to insert a pre-defined value to this column.

    Consider you have the following File table.

    CREATE TABLE File
    (
     FileID int IDENTITY,
     FileName varchar(100),
     Contents varchar(200)
    )

    Now, let us try to insert a record into File table with identity field.

    INSERT INTO File(FileID,FileName,Contents) VALUES(1,'Confidential','Secret')
    

    We will get the following error: Cannot insert explicit value for identity column in table 'File' when IDENTITY_INSERT is set to off.

    In order to allow insert to identity field we need to set IDENTITY_INSERT on as below:

    SET IDENTITY_INSERT File ON
    

    Now the below insert query will run successfully

    INSERT INTO File(FileID,FileName,Contents) VALUES(1,'Confidential','Secret')
    

    After Inserting your own value to identity field we need to set IDENTITY_INSERT OFF as below

    SET IDENTITY_INSERT File OFF
    

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: