How to update Identity Column in SQL Server?

by

You can not update identity column.

SQL Server does not allow to update the identity column unlike what you can do with other columns with an update statement.

Although there are some alternatives to achieve a similar kind of requirement.

  • When Identity column value needs to be updated for new records

Use DBCC CHECKIDENT which checks the current identity value for the table and if it’s needed, changes the identity value.

DBCC CHECKIDENT('tableName', RESEED, NEW_RESEED_VALUE)
  • When Identity column value needs to be updated for existing records

Use IDENTITY_INSERT which allows explicit values to be inserted into the identity column of a table.

SET IDENTITY_INSERT YourTable {ON|OFF}

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 the identity column
INSERT INTO YourTable(IdentityCol, otherCol) VALUES(13,'myValue')
GO
-- Delete the old row of which you have inserted a copy (above) (make sure about FK's)
DELETE FROM YourTable WHERE ID=3
GO
--Now set the idenetity_insert OFF to back to the previous track
SET IDENTITY_INSERT YourTable OFF

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. T-SQL split string
  3. Unpivot with column name
  4. How can I get column names from a table in SQL Server?
  5. Conditional WHERE clause in SQL Server
  6. How can I select the first day of a month in SQL?
  7. Is the NOLOCK (Sql Server hint) bad practice?
  8. Multi-statement Table Valued Function vs Inline Table Valued Function
  9. Convert varchar into datetime in SQL Server
  10. Select statement to find duplicates on certain fields
  11. Check if a row exists, otherwise insert
  12. T-SQL datetime rounded to nearest minute and nearest hours with using functions
  13. Parse comma-separated string to make IN List of strings in the Where clause
  14. SQL query to split column data into rows
  15. Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)
  16. Compare DATETIME and DATE ignoring time portion
  17. Split function in SQL Server 2008
  18. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  19. SQL “between” not inclusive
  20. Using PIVOT in SQL Server 2008
  21. Store multiple bit values in a single table column
  22. Which SQL query is faster? Filter on Join criteria or Where clause?
  23. SQL Server equivalent to MySQL enum data type?
  24. How can I group by date time column without taking time into consideration
  25. Using IF ELSE statement based on Count to execute different Insert statements
  26. When using GETDATE() in many places, is it better to use a variable?
  27. Cumulative Total in MS Sql server [duplicate]
  28. SQL Server SELECT INTO @variable?
  29. COALESCE Function in TSQL
  30. SQL Server: +(unary) operator on non-numeric Strings

Leave a Comment