You tried to put your own value into an identity column and SQL Server refused. The fix is IDENTITY_INSERT, and it takes about ten seconds. The part worth knowing is that there are two different errors here, and which one you get tells you what you actually did wrong.
The Error
Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'dbo.IdDemo' can only be
specified when a column list is used and IDENTITY_INSERT is ON.An identity column belongs to SQL Server. It hands out the next number and expects you to leave it alone. When you supply a value yourself, it stops you.
Reproducing It
This is the whole thing in four lines. It ran on SQL Server 2025.
CREATE TABLE dbo.IdDemo (id int IDENTITY(1,1) PRIMARY KEY, name varchar(20));
INSERT INTO dbo.IdDemo VALUES (5, 'x');No column list, a value for the identity, and you get 8101.
Two Errors, Not One
Read the message again. It asks for two things: a column list, and IDENTITY_INSERT ON. Miss either one and you get an error, but not the same error.
Leave out the column list and you get 8101, the one above. It does not matter whether IDENTITY_INSERT is on or off. I tested both and got 8101 either way.
Use a column list but leave IDENTITY_INSERT off and you get 544 instead:
INSERT INTO dbo.IdDemo (id, name) VALUES (5, 'x');Msg 544, Level 16, State 1
Cannot insert explicit value for identity column in table 'IdDemo' when
IDENTITY_INSERT is set to OFF.So the number tells you which half you forgot. 8101 means write the column list. 544 means turn the setting on. Most people hit 8101 first, fix the setting, and then hit 544 because they never added the column list.
The Fix
SET IDENTITY_INSERT dbo.IdDemo ON;
INSERT INTO dbo.IdDemo (id, name) VALUES (5, 'five');
SET IDENTITY_INSERT dbo.IdDemo OFF;Both halves are there now. The column list names every column you are supplying, and the setting is on. Turn it off again the moment you are finished.
One Table at a Time
This catches people writing a migration script. You can only have IDENTITY_INSERT on for one table per session. Try a second one while the first is still on and you get this:
Msg 8107, Level 16, State 1
IDENTITY_INSERT is already ON for table 'tempdb.dbo.IdDemo'.
Cannot perform SET operation for table 'dbo.IdDemo2'.So a script that loads twenty tables has to turn the setting on and off twenty times, around each one. It is not a switch you flip at the top and forget.
TRY and CATCH Will Not Save You
This one surprised me when I checked it. Wrap the failing INSERT in TRY and CATCH in the same batch and the error still lands in your face. CATCH never runs.
The reason is that 8101 is raised when the statement is bound, before the batch runs, so there is nothing for CATCH to catch yet. If you need to handle it, put the INSERT in its own batch or in dynamic SQL. This is the same reason a typo in a table name escapes CATCH too.
What Happens to the Next Number
A fair question. If you force in id 5, does the counter carry on from 1 and collide later?
No. SQL Server notices and moves the seed up. I inserted 5 by hand, turned the setting off, then did a normal insert. The next row came back as 6, and IDENT_CURRENT agreed.
SELECT IDENT_CURRENT('dbo.IdDemo') AS ident_current;That only works upward. Force in a low number into a table that is already at 900 and the seed stays at 900, which is what you want.
When You Should Not Be Doing This At All
IDENTITY_INSERT exists for moving data: a migration, a restore of deleted rows, seeding a lookup table with known keys. Those are good reasons.
If you find yourself reaching for it in everyday application code, the identity column is probably the wrong choice. You may want a normal column you control, or a sequence, which you can read from before the insert rather than after it.
And be careful about who can do this. Turning IDENTITY_INSERT on needs ownership of the table or ALTER permission on it, which is not a permission most application accounts should be carrying around.
Error 8101 is not the database being awkward, it is the database asking which of two things you forgot.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
60 Comments. Leave new
Hey all, love the post! If like me you hate having to specify column names (as they will no doubt change), the following SQL will work on any table that has PK_FK contraints (where you need to SET IDENTITY_INSERT to ON/OFF and specify column names):
— Create column list as you can’t insert with identity insert = on without specifying column names!
DECLARE @ColumnList varchar(1000)
SELECT @ColumnList = COALESCE(@ColumnList + ‘, ‘, ”) + COLUMN_NAME
FROM information_schema.columns WHERE table_name = ‘Menu’ ORDER BY ORDINAL_POSITION
–Menu
SELECT @sql =
‘SET IDENTITY_INSERT ‘ + @DestinationDB + ‘.dbo.Menu ON;’ +
‘INSERT INTO ‘ + @DestinationDB + ‘.dbo.Menu (‘ + @ColumnList + ‘) SELECT ‘ + @ColumnList + ‘ FROM PizzaHutTemp.dbo.Menu;’ +
‘SET IDENTITY_INSERT ‘ + @DestinationDB + ‘.dbo.Menu OFF;’
EXEC (@sql)
PRINT @sql
thank you, Phil. Love that coalesce trick!
and thank you, Pinal Dave!
Anybody know how to insert a value for indentity column without using
set identity_insert Temp on?
My code looks like:
CREATE TABLE Temp(
ID INT IDENTITY(1,1) )
in this case how can i go for insert statement?
Insert into Temp default values
Hi dave ,
i had a problem with importing database , i have gone through your blog but i didn’t got a suitable solution , so plz go through this and help me in settings things right.
i am using sqlbulkcopy to import data from db2 to sql server 2005.
here my destination table in sql has a coloumn(some id coloumn) with identity specification
so when i import the data it defaultly takes the id and i am loosing the actual id’s (the id ‘s in db2 )
i tried running a sql command with (“set identity_insert tablename on “) before loading and again i reset it to “off” after loading (i.e; after writing to server with bulkcopy object) but for no use .
still i can’t get the original ids (from db2) — i am loosing the data .
i am in a bottle neck situation , so plz kindly suggest me a solution ASAP.
I’ve inserted numerous times in the past to tables that had identity column but I didn’t use column name list.
Stumped myself until I removed the order by at the end of my select statement. Then SQL assigns the identity number and I do NOT have to provide column list.
GENIUS! This has been driving me mad for ages.
Hi All,
Thanks for all those valuable suggestions, it worked for me !!!
SET IDENTITY_INSERT ON
insert into iRSA.CONFIGVAL
Select * from [RIC-NT88].acptImageRIght.iRSA.CONFIGVAL
SET IDENTITY_INSERT OFF
DID NOT WORK FOR ME,,,, THE ONLY THING THAT DID WAS THE FOLLOWING :
GO TO SQL SERVER MANGEMENT STUDIO.
RIGHT CLICK ON TABLE IN QUESTION
— GO TO DESIGN VIEW AND TOGGLE (IS IDENTITY) TO ‘NO'(UNDER IDENTITY SPECIFICATION)
RUN SQL STATEMENT, GO TO DESIGN VIEW AGAIN ,TOGGLE BACK
ANYONE KNOW WHY THE SET STATEMENT DID NOT WORK?
Thanks a ton. that really worked
SET IDENTITY_INSERT iRSA.CONFIGVAL ON
it works for me
For the SQL statement, you also have to specify the column list. For eg.
INSERT INTO tbl (idcol1,col2) VALUES ( value1,value2)
instead of
INSERT INTO tbl VALUES ( value1,value2)
Hi ! In SQL SERVER
SET IDENTITY_INSERT ON/ OFF
doesn’t resolved my problem.
The only way to solve the problem was to specify the column list, ie :
INSERT INTO tbl (idcol1,col2) VALUES ( value1,value2)
Cheers !
Thnx alot !!!
A simple way is to identify all the columns that have the “Allow Duplicates” attribute set to No. This will include the primary indices (e.g. the “ID” column which Access usually creates automatically.)
You then have one of two choices:
1. Turn the “Allow Duplicates” to Yes.
OR
2. Delete the column.
Then
3. Run the Bulk Import.
4. If you’ve selected 2 above, then decide whether you need an index column, and create a new one.
I’ve done 2 and it works. Do it especially if Access has created an index automatically for you – the content of such indices is usually unrelated to the data (any old unique values will do) so you can just build it in the end, as in 4.
how to set “Allow Duplicates” to Yes.
It works for me as well.
Thanks..
See the SQL…
BEGIN TRANSACTION
GO
SET IDENTITY_INSERT Report.dbo.TxTotal ON
GO
INSERT INTO
Report.dbo.TxTotal
(lSequenceNumber,lRetailStoreID,lTaNmbr,lWorkstationNmbr,szDate,szTime,lTaSeqNmbr,lTaCreateNmbr,lTaRefToCreateNmbr,dTotalSale,dTotalNet,dTotalInEuro,lNmbrOfItems,bIsVoidReceipt,lHostInterfaceID1,lHostInterfaceID2,lHostInterfaceID3,lTechLayerAccessID)
SELECT
lSequenceNumber,lRetailStoreID,lTaNmbr,lWorkstationNmbr,szDate,szTime,lTaSeqNmbr,lTaCreateNmbr,lTaRefToCreateNmbr,dTotalSale,dTotalNet,dTotalInEuro,lNmbrOfItems,bIsVoidReceipt,lHostInterfaceID1,lHostInterfaceID2,lHostInterfaceID3,lTechLayerAccessID
FROM TPCentralDB.dbo.TxTotal
WHERE szDate between ‘20121006’ and ‘20130107’
select MIN(szdate), MAX(szdate) from Report.dbo.TxTotal
SET IDENTITY_INSERT Report.dbo.TxTotal OFF
GO
COMMIT TRANSACTION;
GO
if you have still error inserting, do not include in your insert command your id column
ex. tbl_users id, name
insert into users (name) VALUES (‘whatever your value’);
Hi All,
I am trying to transfer data from one database to another both having the same structure using a stored procedure and i am facing this error for tables having identity column as i need to transfer the data as it is even if it is a identity column.Please suggest me a solution.
And i am using stored procedure as per my requirement.
I am getting this error even though i am truncating the table before inserting the data.
Below is the script i am using.
TRUNCATE TABLE TableName
INSERT INTO TableName SELECT * FROM TableName
You need to specify the column names in the INSERT statement
Thanks for sharing your ideas and thoughts, i like your blog.
I am tried following query
SET IDENTITY_INSERT StudentSubjectRegistration ON
GO
Insert Into StudentSubjectRegistration
SELECT StudentSubjectRegistrationID, StudentSemesterRegistrationID, StudentRegCode, DivisionID, SemesterActivationID, SemesterID, BranchesSubjectDetailID,
SubjectDetailsID, SubjectCategoryID, SubjectGroupPID, NoOfAttempt, StudentStatusPID, ParentSubject, SubjectCode, FacultySubjectMappingID, OrgGrade,
OrgGradePoint, TotalMark, IsReExamRegistered, IsAllowReExam, IsEquivalence, IsMedicalReason, AwardedGrade, AwardedGradePoint, SubjectCredit,
EvalutionTypePID, CreditGradePoint, IsFeedBackSubmitted, YearID, CollegeID, IsEnabled, IsAccepted, IsAccRejected, AccComment, IsApproved, IsAppRejected,
AppComment, IsFreezed, IsArchived, CreatedBy, CreatedOn, ModifiedBy, ModifiedOn
FROM OEPIS.dbo.StudentSubjectRegistration
WHERE (StudentRegCode = 111615003) AND (SemesterActivationID = 239) AND (SemesterID = 190) AND (YearID = 12) AND (SubjectCode IN (‘CSE-16005’))
Go
SET IDENTITY_INSERT StudentSubjectRegistration Off
GO
But still error showing in sql
An explicit value for the identity column in table ‘StudentSubjectRegistration’ can only be specified when a column list is used and IDENTITY_INSERT is ON.
Hi,How to display deleted rows in SQL server here no triggers and notification date only three columns in table that is id identity,name varchar(100),age int…..please suggest.