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
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.
I still get the error even after setting IDENTITY_INSERT to ON.
Any other suggestions?
Thank you!
Try specifying the columns names
I think you can fix it follow:
Example:
Use Example
Go
SET IDENTITY_INSERT dbo.Category ON
Go
Insert in to dbo.tblCategory (CategoryID,CategoryName)
Values(2,Story)
Go
Thanks
thanks. I solved this issue by specify the columns names.
Thanks Gaurav!
I also get the same error even after setting IDENTITY_INSERT to ON.
Any other suggestions?
Thank you!
You need to specify the column names in the INSERT statement
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!
Excellent, this syntax worked for me as well, thanks!
That helped me too. Thanks.
Thanks Garrett
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?
It is not possible without specifying the column lists
In a stored procedure doesn’t work.
Anybody found a way to do it?
Thanks
Can you post the code used in the stored procedure?
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;
Thank U, It Works for me…
–####### 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
thanks for ur post its working for me!!!!
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!