IDENTITY_INSERT - 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 2005 Forums

Transact-SQL (2005)

IDENTITY_INSERT

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2010-08-09 : 06:22:52

Hi,

I keep getting a identity insert error and have looked up on google for possible solutions but still no success any help on the following???

DECLARE @extractid INT

SET @extractid = (SELECT ExtractId FROM extract.Extract WHERE ExtractCode = 'Med1D' AND Status = '1')

SET IDENTITY_INSERT extract.ExtractFile ON

INSERT INTO extract.ExtractFile
VALUES (@extractid, '1', '1','1','target','Customer','','','','','','')

and the follow error comes up:
Explicit value must be specified for identity column in table 'ExtractFile' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

Any ideas?

Much appreciated!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-09 : 06:34:39

check if this
SET @extractid = (SELECT ExtractId FROM extract.Extract WHERE ExtractCode = 'Med1D' AND Status = '1')
is setting @extractid to NULL

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

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-09 : 06:39:24

I think you need to specify the columns in the insert statement when doing an explicit insert like this:

INSERT INTO extract.ExtractFile (extractid, col2, col3, ...)
VALUES (@extractid, '1', '1','1','target','Customer','','','','','','')

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2010-08-09 : 06:55:39

I just tried that, i named all the rows and its still doing the same thing.

Also its not setting extractid to null (comment above) i just inserted it into a temp table for testing.

Any other possible ideas?

quote:
Originally posted by Lumbago

I think you need to specify the columns in the insert statement when doing an explicit insert like this:

INSERT INTO extract.ExtractFile (extractid, col2, col3, ...)
VALUES (@extractid, '1', '1','1','target','Customer','','','','','','')

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-09 : 07:14:27

[code]create table mytable (
id int identity (1, 1),
nickname varchar(200)
)
go
insert into mytable select 'lumbago'
go
set identity_insert mytable on
go
insert into mytable (id, nickname) select 2, 'cipriani1984'
go
set identity_insert mytable off
go
select * from mytable
go
drop table mytable
go[/code]

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources