Set IDENTITY_INSERT ON - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Set IDENTITY_INSERT ON

Author

Topic

nns80
Starting Member

9 Posts

Posted - 2004-03-19 : 20:01:38

I am getting the following error message when I execute the sp.

An explicit value for the identity column in table '#Temp' can only be specified when a column list is used and IDENTITY_INSERT is ON.

CREATE sp
AS
--The definition of the table is as shown:

CREATE TABLE #temp
{
Counter INT IDENTITY(0,1) NOT NULL ,
Format INT,
SequenceNumber INT,
RowNumber INT
}
SET IDENTITY_INSERT #Temp ON

--And the values are inserted into it as:
INSERT INTO #temp (12,1,1)
INSERT INTO #temp (13,1,4)
INSERT INTO #temp (12,4,1)
INSERT INTO #temp (16,1,4)
INSERT INTO #temp (13,1,1)

SELECT * FROM #temp
DROP #temp
GO

The purpose of the identity column is to have a row number for each row I insert starting from 0 and proceed further sequentially. Its giving me the above error.

please try to correct this error or suggest an alternative way to keep track of the row numbers starting with 0.

Thanks,
-NS.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-19 : 20:13:58

[code]CREATE TABLE #temp
(
Counter INT IDENTITY(0,1) NOT NULL ,
Format INT,
SequenceNumber INT,
RowNumber INT
)
--SET IDENTITY_INSERT #Temp ON

--And the values are inserted into it as:
INSERT INTO #temp SELECT 12,1,1
INSERT INTO #temp SELECT 13,1,4
INSERT INTO #temp SELECT 12,4,1
INSERT INTO #temp SELECT 16,1,4
INSERT INTO #temp SELECT 13,1,1

SELECT * FROM #temp
DROP TABLE #temp
GO[/code]

nns80
Starting Member

9 Posts

Posted - 2004-03-19 : 20:20:25

I still get the above error "An explicit value for the identity column in table '#Temp' can only be specified when a column list is used and IDENTITY_INSERT is ON" even if I comment the 'set identity_insert #temp ON' line.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-03-19 : 21:45:29

You have to specify the column names in the INSERT clause, without including the identity column:

INSERT INTO #temp (Format, SequenceNumber, RowNumber) VALUES (12,1,1)
INSERT INTO #temp (Format, SequenceNumber, RowNumber) VALUES (13,1,4)
INSERT INTO #temp (Format, SequenceNumber, RowNumber) VALUES (12,4,1)
INSERT INTO #temp (Format, SequenceNumber, RowNumber) VALUES (16,1,4)
INSERT INTO #temp (Format, SequenceNumber, RowNumber) VALUES (13,1,1)

kevin_svek
Starting Member

1 Post

Posted - 2009-10-02 : 03:31:39

And when you turn identity_insert on you also need to turn it off again afterwards

SET IDENTITY_INSERT #Temp Off

so if you have already turned it on - you need to supply the column list or just turn it off again when you don't need to supply the column list.

The fact you've got the error "An explicit value for the identity column in table '#Temp' can only be specified when a column list is used and IDENTITY_INSERT is ON" suggests you didn't turn it off - disconnect your query and run it again with the commented set identity_insert statement and you should be fine (since it's a temp table)

Correct structure if you want to control identity_insert is

SET IDENTITY_INSERT #Temp ON
insert #temp (columnlist) values / select from other table

SET IDENTITY_INSERT #Temp OFF

select * from #temp

B

tripathi_soft
Starting Member

8 Posts

Posted - 2010-06-29 : 04:44:36

Hi,

if you are getting this error>>

An explicit value for the identity column in table DB1.DBO.TABLE1

can only be specified when a column list is used and IDENTITY_INSERT is ON.

Means u are trying to insert some values into a column which has already indentity is on.

Follow given below steps to resolve this..

SET IDENTITY_INSERT DB1.DBO.TABLE1 ON

INSERT INTO DB1.DBO.TABLE1

(COLUMN1,
COLUMN2,
COLUMN3)

SELECT DISTINCT
COLUMN1,
COLUMN2,
COLUMN3

FROM DB2.DBO.TABLE2

SET IDENTITY_INSERT DB1.DBO.TABLE1 OFF

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-29 : 04:49:40

Some more people here who want to give the same answer to a post from 2004?

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources