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

This error occurs when 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:

Before your SQL Statement:
SET IDENTITY_INSERT <tablename> ON

{YOUR SQL INSERT STATEMENT}
After your SQL Statement:
SET IDENTITY_INSERT <tablename> OFF
Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL

About these ads

57 thoughts 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

    • Lets have an Employee table in which some employee records are saved with the unique identity numbers for each employee. Now, suppose some records were deleted from the Employee table due to some reasons and now I want all the deleted records from Employee table,which may not be possible now as records are deleted from the table,but we can get the list of Unique Identity records deleted from Employee table by using query.

      To demonstrate this first of all we have to create an Employee Table with some records inserted to it.Then we can delete some selected records from the Employee table to verify the output.

      http://www.mindfiresolutions.com/How-to-get-the-Deleted-Identity-Numbers-in-SQL-Server-2005-799.php

  1. A solution that worked for me from another site:

    You need a column list for your INSERT statement:

    INSERT t2 (
    [id], [first], [org], [rest], [aux] ) SELECT
    [ie], [first], [org], [rest], [aux] FROM t1


    Tom

  2. Yes, but what if I want to insert without having to use a column list? if i use a column list, then every time a column is added, modified or deleted I need to change this store procedure too, which results in doubling maintenance costs.

    any ideas? how can I do that?

    • then how could i make it in stored procedure I am getting this error in my SP only!!!! kindly help me out how to write in SP pls….

  3. Yes, you can do this in a SPROC. I did something like the following to switch the IDENTITY_INSERT on a table from within a SPROC running in a separate database (SQL Server 2005).

    declare @thisDb varchar(25)
    declare @targetDb varchar(25)

    set @thisDb = ‘db1′
    set @targetDb = ‘db2′

    set @sql = ‘
    use ‘ + @targetDb + ‘;
    set identity_insert t2 on;
    insert into t2 (id, first, org, rest, aux)
    select id, first, org, rest, aux
    from ['+@thisDb+']..t1;
    set identity_insert t2 off;
    use ‘ + @thisDb + ”
    exec(@sql)

    Hope it helps you!

  4. I have the same prob:

    An explicit value for the identity column in table ‘IV_Equipment’ can only be specified when a column list is used and IDENTITY_INSERT is ON.

    I try to run this query 1st before the insert

    SET IDENTITY_INSERT ON

    but still not working…is that due to column list? How should column list be?

    Thank you.

  5. I want to insert data from excel sheet to sql server2005 table.
    i m using this way
    Database(right click)>>task>>Import Data…

    it creates new table with having ‘$’ at end of the table name.

    But i don’t want to create new table i just want to insert into existing table. how is it possible?

  6. It works!! Thanks

    set identity_insert ITEMS_TO_INSERT_BkUp on;
    Insert into ITEMS_TO_INSERT_BkUp(ID,SKU_NO, INSERT_FL) Select ID, SKU_NO, INSERT_FL from ITEMS_TO_INSERT
    set identity_insert ITEMS_TO_INSERT_BkUp Off;

  7. –####### How to Apply Identity_Insert Specification on Existing Column(Field)of a Table ########

    /*
    Step 1

    Make a new same schema for NewTable as in the OldTable
    Note : OldTable – Which has not set the Identity in the ID Field)
    e.g. Select * Into NewTable from OldTable where 1=2

    Step 2

    Go to the Database–>Tables–>Right click on the Table and then Click on Modify Option

    In the Modify option

    A. Uncheck the AllowNull Option
    B. Set the identity_Specification ‘Yes’ instead of ‘No’
    c. Save the table Structure(Schema)

    Step 3

    In Query Analyser Write a T-SQL Script

    Set Identity_Insert dbo..NewTable Off

    Step 4

    In Query Analyser Write a T-SQL Script to Insert all record into the NewTable from OldTable

    insert into NewTable (column1,Column2,Column3,———–ColumnN)
    select column1,Column2,Column3,———–ColumnN from OldTable

    */

    Thanks,

    Parmod Bhardwaj
    Gurgaon

  8. HI,

    I just add the column list , and I can use Stored Procedures to add identity columns:

    I have DateCategory table:
    CREATE TABLE [dbo].[DataCategory](
    [CategoryNo] [int] IDENTITY(1,1) NOT NULL,
    [CategoryType] [varchar](100) COLLATE SQL_Latin1_General_CP437_CI_AS NOT NULL,
    [CategoryID] [varchar](100) COLLATE SQL_Latin1_General_CP437_CI_AS NULL,
    [CategoryDescription] [varchar](100) COLLATE SQL_Latin1_General_CP437_CI_AS NULL,
    CONSTRAINT [PK_DataCategory] PRIMARY KEY CLUSTERED
    (
    [CategoryNo] ASC
    )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    ——————–
    Then I can insert identity rows buy add in the column list:

    INSERT INTO DataCategory(CategoryNo, CategoryType,CategoryID,CategoryDescription) VALUES(@CategoryNo, ‘Alarm ‘, EQ1, ‘Alarm for EQ1′)

  9. Hi, I just add the column lists after the table name and I can insert identity table:

    I have this table DataCategory:
    CREATE TABLE [dbo].[DataCategory](
    [CategoryNo] [int] IDENTITY(1,1) NOT NULL,
    [CategoryType] [varchar](100) COLLATE SQL_Latin1_General_CP437_CI_AS NOT NULL,
    [CategoryID] [varchar](100) COLLATE SQL_Latin1_General_CP437_CI_AS NULL,
    [CategoryDescription] [varchar](100) COLLATE SQL_Latin1_General_CP437_CI_AS NULL,
    CONSTRAINT [PK_DataCategory] PRIMARY KEY CLUSTERED
    (
    [CategoryNo] ASC
    )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    Then I add the column list and can use this query to insert data :

    INSERT INTO DataCategory(CategoryNo, CategoryType,CategoryID,CategoryDescription) VALUES(@CategoryNo, ‘Alarm’, ‘EQ’, ‘Alarm for EQ1′)

  10. For all the ones that have tried the command and are still getting the same error message, what you have to do is to explicitly add all the column names (including the ID one) to your insert statement.

    SET IDENTITY_INSERT ON

    INSERT INTO (, , ) VALUES (, , )

    SET IDENTITY_INSERT OFF

    Regards,
    Morrones

  11. Hi
    I got the same .. But what i did was before I used Select * for the inserting.. but now I manualy put all the column names in my insert and select query and it worked.

  12. Hi All, I
    don’t know if this helps.

    This stored Procedure inserts in table [user], copy to table [copyuser] and finnaly, to delete from [user].

    CREATE PROCEDURE copyto
    @id int

    AS

    SET IDENTITY_INSERT usersCopy off
    insert into userscopy (first,last)values (‘first’,’last’)
    SET IDENTITY_INSERT users on
    insert users ([id],[first]) select [id],[first] from usersCopy where id=@id
    delete from userscopy where id=@id

  13. 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

  14. 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?

  15. 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.

  16. 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.

  17. 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?

  18. 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)

  19. 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 !

  20. 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.

  21. 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

  22. 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’);

  23. 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.

  24. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s