SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Set IDENTITY_INSERT ON
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nns80
Starting Member

9 Posts

Posted - 03/19/2004 :  20:01:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1630 Posts

Posted - 03/19/2004 :  20:13:58  Show Profile  Reply with Quote
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
Go to Top of Page

nns80
Starting Member

9 Posts

Posted - 03/19/2004 :  20:20:25  Show Profile  Reply with Quote
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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 03/19/2004 :  21:45:29  Show Profile  Visit robvolk's Homepage  Reply with Quote
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)
Go to Top of Page

kevin_svek
Starting Member

United Kingdom
1 Posts

Posted - 10/02/2009 :  03:31:39  Show Profile  Reply with Quote
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
Go to Top of Page

tripathi_soft
Starting Member

India
8 Posts

Posted - 06/29/2010 :  04:44:36  Show Profile  Reply with Quote
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
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 06/29/2010 :  04:49:40  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000