How to Insert Values to Identity Column in SQL Server

In this blog you will learn how to insert values to identity column in SQL Server.

Introduction

This article gives you a clear spotlight on how to insert the values into 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?

The 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 into 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 the Customer table with the 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 insertion into the identity field

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

SET IDENTITY_INSERT Customer OFF

Insert Value to the Identity field

Now, let’s see how to insert our own values to identify field ID within 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 into the 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 into the 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 newly 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.