How to Insert Values to Identity Column in SQL Server

Introduction:

This article gives you a clear spotlight on how to insert the values to the identity column and in this article I have explained things by creating a simple table, also I have done the simple insert operation and also explained the behavior of the SQL.

How to execute the Query?

Identity field is usually used as a primary key.When you insert a new record into your table, this field automatically assigns an incremented value from the previous entry. Usually, you can't insert your own value to this field. Consider you have the following Customer table.

CREATE TABLE Customer

(

ID int IDENTITY,

Name varchar(100),

Address varchar(200)

)

Try inserting a record into Customer table with identity field as below,

INSERT INTO Customer(ID, Name, Address)

VALUES(1,'Mili',Chennai')

then I will get the error message.

Allow insert into identity field

You can allow insert to the identity field by setting IDENTITY_INSERT ON for a particular table as shown:

SET IDENTITY_INSERT Customer ON

Disallow insert into identity field

You can also disallow insert to the identity field by setting IDENTITY_INSERT OFF for a particular table as shown:

SET IDENTITY_INSERT Customer OFF

Insert Value to Identity field

Now, let’s see how to insert our own values to identity field ID with in the Customer table.

SET IDENTITY_INSERT Customer ON
INSERT INTO Customer(ID, Name, Address)
VALUES(3,'Prabhu','Pune')
INSERT INTO Customer(ID, Name, Address)
VALUES(4,'Hrithik','Pune')
SET IDENTITY_INSERT Customer OFF
INSERT INTO Customer(Name, Address)
VALUES('Ipsita', 'Pune')

After Inserting your own value to identity field don't forget to set IDENTITY_INSERT OFF.

Note

 

1. Usually, we use this when we have deleted some rows from the table and we want the data in a sequence.
2. After Inserting your own value to identity field don't forget to set IDENTITY_INSERT OFF

Reseed the Identity field

You can also reseed the identity field value. By doing so identity field values will start with a new defined value. Suppose you want to reseed the Customer table ID field from 3 then the new records will be inserted with ID 4,5,6..and so on.

DBCC checkident (Customer, RESEED, 3)

INSERT INTO Customer(Name, Address)

VALUES('Bhuvan','Bombay')

Hope this article may be simple and gave you the idea of SQL Query.