identitiy insert

Im having trouble enabling identity insert in a stored procedure when transferring data between two databases



-----------------------------------------------------------------------
-- Procedure: dbo.deadline_performSubstep_NVDDictionaryOption
--
-- Deadlines the NVDDictionaryOption tables in their entirety
--
-- NVDDictionaryOption-> NVDDictionaryOption
--
-- 14/08/2003.PAC - Initial Version
-----------------------------------------------------------------------
CREATE PROCEDURE dbo.deadline_performSubstep_NVDDictionaryOption
AS
BEGIN
DECLARE @ErrorCode int

truncate table DEAD_CAR.dbo.NVDDictionaryOption
SET IDENTITY_INSERT DEAD_CAR.dbo.NVDDictionaryOption ON

insert into
DEAD_CAR.dbo.NVDDictionaryOption
select
DO_OptionCode,
DO_ManLevel,
DO_CatCode,
DO_Description,
DO_LongDescription,
DO_NonSpecificCostOption
from
CAR.dbo.NVDDictionaryOption

SET IDENTITY_INSERT DEAD_CAR.dbo.NVDDictionaryOption OFF
RETURN @@ERROR
END
GO

I get the error



Error 8101: An explicit value for the identity column in table 'DEAD_CAR.dbo.NVDDictionaryOption' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Can anyone advise a solution ?

BOL states -

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

Do you think this is the problem?

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Thanks guys, but neither of these appear to be the problem. As far as I know no other table has identity_insert on as the error im getting does not indicate this to be the case.<br /><br />I thought maybe the use of a second database was confusing the parser, so I duplicated CAR.NVDDictionaryOption to CAR.NVDDictionaryOption2 and tried the transfer within a single database.<br /><br />Again I get the same error.<br />--<br />An explicit value for the identity column in table 'NVDDictionaryOption2' can only be specified when a column list is used and IDENTITY_INSERT is ON.<br /><br /><pre><br />truncate table NVDDictionaryOption2<br />SET IDENTITY_INSERT NVDDictionaryOption2 ON <br />insert into <br />NVDDictionaryOption2<br />select <br />"DO_OptionCode"=DO_OptionCode,<br />"DO_ManLevel"=DO_ManLevel,<br />"DO_CatCode"=DO_CatCode,<br />"DO_Description"=DO_Description,<br />"DO_LongDescription"=DO_LongDescription,<br />"DO_NonSpecificCostOption"=DO_NonSpecificCostOption<br />from<br />NVDDictionaryOption<br />SET IDENTITY_INSERT NVDDictionaryOption2 OFF<br /></pre><br /><br />this fails too, even outside of a stored procedure <img src='/community/emoticons/emotion-6.gif' alt='' />

Ah.. it seems the clue was in the error message. It was asking for a column list, which I assumed it meant my select. It didnt occur to me what it wanted the inserted columns explicitly stated.



truncate table NVDDictionaryOption2
SET IDENTITY_INSERT NVDDictionaryOption2 ON

insert into
NVDDictionaryOption2
(DO_OptionCode, DO_ManLevel, DO_CatCode, DO_Description, DO_LongDescription, DO_NonSpecificCostOption)
select
DO_OptionCode,
DO_ManLevel,
DO_CatCode,
DO_Description,
DO_LongDescription,
DO_NonSpecificCostOption
from
NVDDictionaryOption

SET IDENTITY_INSERT NVDDictionaryOption2 OFF

Works a treat. Thanks

As the error suggests you have to use the column list in the insert command when you are trying to explicitly insert values into an Identity column.

For example in your case, you have to modify your statement as below:

insert into
DEAD_CAR.dbo.NVDDictionaryOption (DO_OptionCode,
DO_ManLevel,
DO_CatCode,
DO_Description,
DO_LongDescription,
DO_NonSpecificCostOption)
select
DO_OptionCode,
DO_ManLevel,
DO_CatCode,
DO_Description,
DO_LongDescription,
DO_NonSpecificCostOption
from
CAR.dbo.NVDDictionaryOption

I am assuming here that you have same column names in both tables.

I agree with ykchakri. Try specifying the name of the columns in the insert statement and then execute the query.

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Sorry Chappy,<br /><br />I think I was bit late in posting the answer <img src='/community/emoticons/emotion-1.gif' alt='' />

Thats ok. Thanks for taking the time anyway <img src='/community/emoticons/emotion-1.gif' alt='' />