Solved

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

Posted on 2004-08-17
5
1,449 Views
Last Modified: 2012-08-14
hi,

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!

thanks,
0
Comment
Question by:gdoherty
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 10

Expert Comment

by:AaronAbend
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.
0
 
LVL 10

Assisted Solution

by:AaronAbend
AaronAbend earned 70 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).

0
 
LVL 3

Accepted Solution

by:
alexpreston earned 55 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...
INSERT INTO [table]
(SELECT 10, field2, field3 FROM [table] WHERE field1 = 3)
0
 

Author Comment

by:gdoherty
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.
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

707 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