This error occurs when the user has attempted to insert a row containing a specific identity value into a table that contains an identity column. Run following commands according to your SQL Statement. Let us learn about the IDENTITY_INSERT.
Before your SQL Statement:
SET IDENTITY_INSERT <tablename> ON
{YOUR SQL INSERT STATEMENT}
After your SQL Statement:
SET IDENTITY_INSERT <tablename> OFF
Let me know if this resolves the problem for you. I honestly think identity should be used very carefully and they should not reset without proper purpose.
Here are few additional references for you to follow up on the subject of the identity:
- SQL SERVER – DBCC command to RESEED Table Identity Value – Reset Table Identity
- SQL SERVER – DELETE, TRUNCATE and RESEED Identity
- SQL SERVER – Jump in Identity Column After Restart
- SQL SERVER – Answer – Value of Identity Column after TRUNCATE command
- SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 6 of 31
- Interview Question of the Week #019 – How to Reset Identity of Table
- SQL SERVER – Finding Out Identity Column Without Using Column Name
- SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record
- SQL SERVER – RESEED Identity Column in Database Table – Rest Table Identity Value – SQL in Sixty Seconds #051
- SQL SERVER – Add Auto Incremental Identity Column to Table After Creating Table
Reference: Pinal Dave (https://blog.sqlauthority.com)
62 Comments. Leave new
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 admin and people nice forum indeed. how’s life? You are great!
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.