Solved

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

Posted on 2004-08-17
5
1,443 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
  • 2
5 Comments
 
LVL 10

Expert Comment

by:AaronAbend
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now