SQL SERVER – Fix : Error 8101 An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON

SQL SERVER - Fix : Error 8101 An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON Identity 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:

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
SQL SERVER – Fix : Error 701 There is insufficient system memory to run this query
Next Post
SQL SERVER – Difference between DISTINCT and GROUP BY – Distinct vs Group By

Related Posts

62 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

    Reply
  • 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?

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • hi admin and people nice forum indeed. how’s life? You are great!

    Reply
  • Hi All,

    Thanks for all those valuable suggestions, it worked for me !!!

    Reply
  • 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?

    Reply
  • SET IDENTITY_INSERT iRSA.CONFIGVAL ON

    Reply
  • it works for me

    Reply
  • 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)

    Reply
  • 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 !

    Reply
  • Mehboob Ali Yousafzai
    October 9, 2010 2:11 am

    Thnx alot !!!

    Reply
  • 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.

    Reply
  • how to set “Allow Duplicates” to Yes.

    Reply
  • PURVESH PRAJAPATI
    March 24, 2013 8:18 pm

    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

    Reply
  • 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’);

    Reply
  • 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.

    Reply
  • 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

    Reply
  • Thanks for sharing your ideas and thoughts, i like your blog.

    Reply
  • sharad bhadalkar
    June 4, 2018 4:40 pm

    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.

    Reply

Leave a Reply

Menu