Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • How to insert value in an auto incremented column in SQL

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 760
    Comment on it

    How to insert value in an auto incremented column in SQL

    Sometimes we have situation to insert value in an auto incremented column. Let's discuss this with a simple example.

    Example

    1) Here is my table structure.

    CREATE TABLE Student(
    Id INT IDENTITY(1,1),
    Name VARCHAR(10)
    )

    2) And I have some entries in my table.

    3) Now if  I delete some entries and insert new record then new record will come with Id = 6. I have deleted record with Id = 3.

    4) Now if I want to insert new record with Id = 3 it will give error as Id column is auto incremented. To do this we have to set  IDENTITY_INSERT to ON and after insert we can set it OFF to disallow manual insert.

    Syntax

    SET IDENTITY_INSERT TableName ON
    INSERT INTO TableName (Col1, Col2...) VALUES (Val1, Val2...)
    SET IDENTITY_INSERT TableName OFF
    
    SET IDENTITY_INSERT Student ON
    INSERT INTO Student (Id, Name) VALUES (3,'Aneesh')
    SET IDENTITY_INSERT Student 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: