October 20, 2011 at 8:54 am
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
October 20, 2011 at 8:58 am
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/
October 20, 2011 at 9:03 am
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
October 20, 2011 at 9:04 am
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/
October 20, 2011 at 9:05 am
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/
October 20, 2011 at 10:22 am
Thanks for prompt response. The issue here is, the identity_insert is ignored thus the error. Even if I run it on ssms.
October 20, 2011 at 10:25 am
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/
October 21, 2011 at 1:54 am
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.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply