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