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
- Usually, we use this when we have deleted some rows from the table, and we want the data in a sequence.
- 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.