If you’re getting an error that reads “An explicit value for the identity column in table ‘Dogs’ can only be specified when a column list is used and IDENTITY_INSERT is ON” in SQL Server, it appears that you’re trying to insert a value into an IDENTITY column.

Specifically, you’re trying to do that while the IDENTITY_INSERT option is set to OFF.

To fix this issue, either enable IDENTITY_INSERT before inserting the value, or omit the value from your list of values to insert (and let the IDENTITY column do it’s thing).

Example of Error

Suppose we create a table with an IDENTITY column:

CREATE TABLE Dogs (
    DogId INT IDENTITY PRIMARY KEY,
    DogName VARCHAR(50)
);

The DogId column is our IDENTITY column and primary key.

Here’s an example of code that leads to the error:

INSERT INTO Dogs VALUES (20, 'Wag');

Output:

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

Here, I tried to insert an explicit value into an IDENTITY column, and I got the error.

I got the error because an IDENTITY column’s main purpose is to automatically populate the column with an incremental value. So in a sense, I gave SQL Server conflicting instructions – something like “hey SQL Server, can you automatically populate this field with your own value… oh and by the way, can you put this value in there instead”. No wonder SQL Server returned an error!

Solution 1

The easiest solution is to let SQL Server do its thing and auto-populate the column with the next incremental value. To do this, we simply don’t provide a value for the IDENTITY column:

INSERT INTO Dogs VALUES ('Wag');

Output:

(1 row affected)

However, it’s usually a good idea to include the column list too. Like this:

INSERT INTO Dogs (DogName) VALUES ('Fetch');

Output:

(1 row affected)

Both of those inserted the dogs into the table using the automatically generated value provided by the IDENTITY column.

Solution 2

If you really must override the IDENTITY column and insert your own value, then you can set the IDENTITY_INSERT option to ON, before doing your insert:

SET IDENTITY_INSERT Dogs ON;
INSERT INTO Dogs (DogId, DogName) VALUES (20, 'Bark');
SET IDENTITY_INSERT Dogs OFF;

Output:

(1 row affected)

Fixed. The insert has happened.

Note that I disabled IDENTITY_INSERT after doing my insert. This option can only work on one table at a time within a given session, so if you try to use it on another table while it’s still enabled on this table, you’ll get an error.

Also note that, once you’ve inserted your explicit value, the IDENTITY column will use that as a basis to increment from for its next value. Therefore, if we insert another row (using the default IDENTITY value) and then select all contents of the table:

INSERT INTO Dogs (DogName) VALUES ('Howl');
SELECT * FROM Dogs;

Output:

DogId  DogName
-----  -------
1      Wag    
2      Fetch  
20     Bark   
21     Howl   

We can see that the values in the DogId column jumped to our explicit value, and then continued incrementing from that value.

Still Getting the Error?

If you’re still getting the error despite setting IDENTITY_INSERT ON, it could be that you’ve failed to provide a column list. When we insert an explicit value into an IDENTITY column, we must provide a column list, otherwise we’ll get the same error:

SET IDENTITY_INSERT Dogs ON;
INSERT INTO Dogs VALUES (22, 'Pant');
SET IDENTITY_INSERT Dogs OFF;

Output:

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

So be sure to include the column list:

SET IDENTITY_INSERT Dogs ON;
INSERT INTO Dogs (DogId, DogName) VALUES (22, 'Pant');
SET IDENTITY_INSERT Dogs OFF;

Something to bear in mind.