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
March 28, 2007 by pinaldave
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
Like this:
Be the first to like this post.
Posted in Pinal Dave, SQL, SQL Authority, SQL Documentation, SQL Download, SQL Error Messages, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Tips and Tricks, T SQL, Technology | 39 Comments
Leave a Reply
Thank you for ur valuable information
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
I still get the error even after setting IDENTITY_INSERT to ON.
Any other suggestions?
Thank you!
Try specifying the columns names
I also get the same error even after setting IDENTITY_INSERT to ON.
Any other suggestions?
Thank you!
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
Thanks for the help, Tom. This was exactly what I needed!
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?
In a stored procedure doesn’t work.
Anybody found a way to do it?
Thanks
Hi All,
You can not use this in Stored Procedure.
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
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!
Thanks for the tip!
Garrett – Thanks too that worked for me.
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.
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?
Hai Pinal Dave …. ur simply superb …
Thanks for this thread .. its 100% solved my problem
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;
–####### 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
Thank to your suggestions, I solved this stupid problem in a stored proc after about two days of tentatives !
Many thanks !!!
Camillo
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′)
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′)
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
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.
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
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?
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.