How to insert values into IDENTITY column in SQL Server (Fixing Errors 544, 8101 and 8107)

Identity columns are often used as primary keys, and a value is automatically inserted into an Identity column for each new row inserted into the table.
If you try to insert a value into the Identity column using this code (in the next example ID column is the Identity column):

INSERT INTO Table_Name (ID, Value)
VALUES (5, 'E')
GO

...you will receive the following error:

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

If you try to insert a new value into the Identity column without specifying the columns in the INSERT statement:

INSERT INTO Table_Name
VALUES (5, 'E')
GO

...you will get a different error message:

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

One way to avoid the error messages is to let SQL Server assign the next identity value. SQL Server will assign the next identity if you don't include the identity column in the INSERT statement:

INSERT INTO Table_Name (Value)
VALUES ('E')
GO

If you want to specify the new value for the identity column here is the solution:
First, you need to enable IDENTITY_INSERT for the table, and then insert values into the Identity column:


SET IDENTITY_INSERT Table_Name ON

INSERT INTO Table_Name (ID, Value)
VALUES (5, 'E')

SET IDENTITY_INSERT Table_Name OFF

Only one table in a session can have the IDENTITY_INSERT property set to ON.
If one table has IDENTITY_INSERT property set to ON and you issue it on another table, you will get the following error message:

Msg 8107, Level 16, State 1, Line 2
IDENTITY_INSERT is already ON for table 'Table_Name_1'. Cannot perform SET operation for table 'Table_Name_2'.

User that issues the statement must be member of the sysadmin fixed server role, the db_owner or member of the db_ddladmin fixed database role, or the object owner.

If you insert the value into the Identity column that is larger than the current identity value for the table, the new inserted value becomes the current identity value.
To see how to reset the table identity value, read related article:
Reset table identity value in SQL Server with DBCC RESEED

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">