Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dhw
Constraint Violating Yak Guru
332 Posts |
 Posted - 2002-12-09 : 13:09:09
|
| I have a table with a primary key column that is set as an Identity type. This works great...but unfortunately, I didn't set my initial value and the values started with 1. I would like to reserve values....of let's say, below 500 for future internal use. Is there any way to update or re-generate the Identity values for each row in my table? I try to run an UPDATE command, but this failed. I am planning on just dumping the data to a temp table and then re-inserting it into an empty table with the initial Identity value set to 500...but if there is a better way I would like to try that.thanks...Douglas |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-12-09 : 14:59:33
|
| I don't believe you can UPDATE an IDENITITY column.Reinsert the data (from a temp table or other source).Jay White{0} |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-12-10 : 12:42:47
|
| 2 comments...1. not a good idea to reserve special value for later use.... in future years, people will wonder what's "special" about customers with numbers < 500....and then wonder what happens with the number approaches 499....(in a way it's a Y2K-style problem in the offing) better to add some flag/column which holds 2/3 values..internal/external, etc...2. based on your premise that the id field is an identity column....these numbers increase...automatically with each insert.pray tell how you plan to insert record with a key of 2 (or 3) when the next record number is due to be 507 or 1008...simply?try it!it involves turning off the identity feature (which you've installed for a reason), inserting the 'special' record and then turning back on the identity feature.q. what happens when a regular 'joe user' inserts a regular customer using normal code....during this time-period??a. it'll fail. the insert statement which works with identity-turned on, doesn't work with it turned off....(try it and see!!!)you're lining up problems for yourself with this strategy.....'special/reserved' values are best dealt with by formally modelling them in your data model....as proper attributes of the entity(table), not associated with some private coding convention based on controlling and manipulating values in data.good luck!!!! |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-12-10 : 14:06:31
|
quote:
q. what happens when a regular 'joe user' inserts a regular customer using normal code....during this time-period??a. it'll fail. the insert statement which works with identity-turned on, doesn't work with it turned off....(try it and see!!!)
I concur with the spirit of Andrew's post, however, the above quote is a bit misinformed. 'joe user's' insert will not fail. It will increment the last identity by the increment value.create table identtest (i int identity(1,1) not null, a char(1))insert identtest values('a')goset identity_insert identtest ongoinsert identtest(i,a) values(500,'b')go ... this should leave you with (1,a) and (500,b). In another session if you duplicate the first insert above you should see (501,a).Jay White{0} |
|
|
|
|
|
|
|
|
Possible to update existing IDENTIY values?