Set Insert_Identity on/off

Question

Set Insert_Identity on/off

thulani.moyana 79207

SSC Eights!

Points: 933
More actions
October 20, 2011 at 8:54 am

#259026

I'm trying to insert records to a table with identity column. I first set it to on and try to insert which fails with the error
cannot explicitly insert identity. This was within a stored proc and I thought I needed a "go" statement and have learnt that I cannot use a "GO" within a stored proc. Try to execute the "Set identity_insert tablename on GO" using exec() function and got an error.
The syntax is not an issue here, I can execute the insert statement successfully without inserting identity. The problem is when I want to insert identity.
Thanks in advance,
Thulani

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286602 More actions · Answer 1

You can use that in your stored proc withOUT the need for a GO or dynamic sql. Just make sure to set it off again before you exit.

--edit correction thanks to Phil.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Phil Parkin SSC Guru Points: 247158 More actions Follow · Answer 2

Sean Lange (10/20/2011)
You can use that in your stored proc with** the need for a GO or dynamic sql. Just make sure to set it off again before you exit.

**without

Sean Lange SSC Guru Points: 286602 More actions · Answer 3

Like this:

create table InsertTest

(

ID int identity not null,

SomeValue varchar(10)

)

go

create procedure IdentityTest

as begin

set identity_insert InsertTest on

insert InsertTest (ID, SomeValue) Values (10, 'MyValue')

set identity_insert InsertTest on

end

go

exec IdentityTest

select * from InsertTest

For real code you should put this inside a try catch.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286602 More actions · Answer 4

Phil Parkin (10/20/2011)
Sean Lange (10/20/2011)
You can use that in your stored proc with** the need for a GO or dynamic sql. Just make sure to set it off again before you exit.
**without

oops thanks for catching the typo. I will correct it.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

thulani.moyana 79207 SSC Eights! Points: 933 More actions · Answer 5

Thanks for prompt response. The issue here is, the identity_insert is ignored thus the error. Even if I run it on ssms.

Sean Lange SSC Guru Points: 286602 More actions · Answer 6

Can you post your proc code?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

thulani.moyana 79207 SSC Eights! Points: 933 More actions · Answer 7

Thanks everyone. The problem is sorted. I had two databases and was trying to move data across. I was setting Identity_isert on for the current database instead of the destination database.

Set Insert_Identity on/off

Cookies on SQLServerCentral