[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1453
  • Last Modified:

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

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
gdoherty
Asked:
gdoherty
  • 2
2 Solutions
 
AaronAbendCommented:
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
 
AaronAbendCommented:
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
 
alexprestonCommented:
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
 
gdohertyAuthor Commented:
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now