SET IDENTITY_INSERT ON won't let me UPDATE an Identity column

Posted on 2004-08-17
Medium Priority
Last Modified: 2012-08-14

i'm hoping to update an Identity column by turning off identity briefly, updating my data, then turning identity back on.
i know i can insert when i turn IDENTITY_INSERT ON, but can't UPDATE - is there an equivalent available in SQL Server 2000 T-SQL that will let me UPDATE, without having to go turn identity off for the column, UPDATE, then turn it back on?

no crazy rush on this, hence the no crazy point allocation...and i suspect the answer is 'can't be done', but would love to hear otherwise!

Question by:gdoherty
  • 2
LVL 10

Expert Comment

ID: 11819526
It cannot be done. It is in the manual for Sybase, and that has the same architecture as MS SQL Server. It might be in MS's doc as well, somewhere.
LVL 10

Assisted Solution

AaronAbend earned 280 total points
ID: 11819596
Naturally you could rebuild the table

create table newtab ... (same structure without identity specified)
insert newtab select * from oldtab
exec sp_rename originaltabname, savetab
exec sp_rename newtab, originaltabname

(Note that some operations that Enterprise Manager "lets" you do will do a table rebuild in exactly this way. Since update is not an EM function, that does not apply specifically here, but removing identity from a column would be. Click the button that shows the SQL before you execute it in EM and it will show you the rebuild).


Accepted Solution

alexpreston earned 220 total points
ID: 11820588
You could turn identity insert on, re-insert the existing row with a new id, and then delete the old one.
e.g. for new id of 10, old id 3, something like...
(SELECT 10, field2, field3 FROM [table] WHERE field1 = 3)

Author Comment

ID: 12110929
thanks guys,

seems to be a hole in ms sql server...would be handy, but your answers have shown me that can't be done.

many thanks/sorry for the delay.

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

587 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question