Tutorial :Update autoincrement column in SQL server


I have a need to change ONE PK value in one row of a table of a SQLSERVER 2005 databsae. The PK is currently auto-incrementing.

One possibility is to temporarily remove the auto-increment from the key, make the modification, and put it back.

The database is in production. Is there a way to safely make this change without taking the DB down?

Why? My customer wants a specific ID for one of the records.

What am I afraid of? I'm afraid of records being added in the DB while I've modified the key, and it somehow messing up an index on another table that uses the field as an FK? Am I nuts?


Updating doesn't work - you'd have to insert the new record and delete the old one instead...

set identity_insert mytable on    insert mytable (myidentitycolumn, someothercolumn)   select 42, someothercolumn  from mytable  where myidentitycolumn = 1;    delete mytable where myidentitycolumn = 1;    set identity_insert mytable off  


You can only update via dropping the identity and re-creating it. However, you could also set identity insert ON (http://msdn.microsoft.com/en-us/library/aa259221(SQL.80).aspx) and insert a new row with the identity val you want, turn ident insert off, then delete the old one. Why do you want to do this?


SET IDENTITY_INSERT yourTable ON    INSERT INTO yourTable (col1, col2, col3)  select newID, col2, col3  FROM yourTable  WHERE currentID = xxx    SET IDENTITY_INSERT yourTable OFF  

You may want to lock the table to prevent other inserts from failing while IDENTITY_INSERT is on.

EDIT: Well crap, I forgot the obvious bit about deleting the original record.


DECLARE @counter int  SET @counter = 0  UPDATE #tmp_Users  SET @counter = counter = @counter + 1  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »