How to Do Identity Insert in SQL Server

Here in this blog post, we discuss how to insert data in data base table by enabling the identity insert then to disable the identity Insert.

How to Do Identity Insert in SQL Server

if a column in a table is auto increment column, then SQL Server will not allow to insert value into that column. Data can be inserted into the remain columns of the table expect the autogenerated table.

Because by default the identity insert is set to off for that column when you mark the column auto increment (identity) while creating table or adding column to the table belongs to the column.

If you try to insert data into the identity column of the table forcefully, then you will get error like below

 Msg 544, Level 16, State 1, Line 1  
 Cannot insert explicit value for identity column in table StudentMaster when IDENTITY_INSERT is set to OFF.

identity insert sql server

Here the following code example describes how to insert data in data base table by enabling identity insert.

 SET IDENTITY_INSERT StudentMaster ON  
 INSERT INTO StudentMaster(Id,Name,Class,Divistion,Gender,ModifiedDate) VALUES(10,'Kailash'Blogs',2,'A','Male','2024-08-16 22:11:06.357')  
 SET IDENTITY_INSERT StudentMaster OFF

identity insert sql server

In the above SQL query, after inserting values to table the identity insert is set to off immediately to work the auto increment function of the column.

Syntax:

 SET IDENTITY_INSERT [ [ database_name . ] schema_name . ] table_name { ON }  
 INSERT INTO table_name(cols) Values(vals)  
 SET IDENTITY_INSERT [ [ database_name . ] schema_name . ] table_name { OFF }

identity insert sql server

Arguments:

database_name : Is the name of the database in which the specified
table resides. schema_name : Is the name of the schema to which the table belongs.
table_name: Is the name of a table with an identity column.
cols: list of columns in comma separated vals: list of values in comma separated

Note that while doing identity insert, have to mention the column name of the table other wise you will get error like below.

 Msg 8101, Level 16, State 1, Line 2  
 An explicit value for the identity column in table StudentMaster can only be specified when a column list is used and IDENTITY_INSERT is ON.

identity insert sql server

Table Structure:

 CREATE TABLE StudentMaster  
 (  
     --identity_insert is set to off
      Id INT IDENTITY(1,1) NOT NULL,  
      Name NVARCHAR(100),  
      Class NVARCHAR(10),  
      Divistion VARCHAR(2),  
      Gender NVARCHAR(6),  
      ModifiedDate DATETIME  
 )

The identity insert for the column Id is set to off , means user can't insert data to the column while inserting data in table.

How to Do Identity Insert in SQL Server