Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Tuesday, 17 February 2015

How to update Identity Column in SQL Server?

update table
set identity_column_name = some value
It is not an easy process and it is not advisable to use it, as there may be some foreign key associated on it.
 You can't update the Identity column as you update the normal ones
But here are steps to do it, Please take a back-up of table
Step 1- Select design view of the table
enter image description here
Step 2- Turn off the identity column
enter image description here
Now you can use the update query.
Now redo the step 1 and step 2 and Turn on the identity column
(OR)
DBCC CHECKIDENT('tableName', RESEED, NEW_RESEED_VALUE)
If you want to update the value of identity column of existing records then you need to set
set identity_insert YourTable ON
Example
-- Set Identity insert on so that value can be inserted into this column
SET IDENTITY_INSERT YourTable ON
GO
-- Insert the record which you want to update with new value in identity column
INSERT INTO YourTable(IdentityCol, otherCol) VALUES(13,'myValue')
GO
--Now set the idenetity_insert OFF to back to prevoius track
SET IDENTITY_INSERT YourTable OFF