{"id":200761,"date":"2024-02-29T17:56:32","date_gmt":"2024-02-29T17:56:32","guid":{"rendered":"https:\/\/namso-gen.co\/blog\/how-to-change-identity-column-value-in-sql-server\/"},"modified":"2024-02-29T17:56:32","modified_gmt":"2024-02-29T17:56:32","slug":"how-to-change-identity-column-value-in-sql-server","status":"publish","type":"post","link":"https:\/\/namso-gen.co\/blog\/how-to-change-identity-column-value-in-sql-server\/","title":{"rendered":"How to change identity column value in SQL Server?"},"content":{"rendered":"

There are times when you may need to change the value of an identity column in SQL Server. This can be achieved by temporarily disabling the identity property on the column, updating the value, and then enabling the identity property once again. Follow the steps below to change the identity column value:<\/b><\/p>\n

1. **Disable the Identity Property**
\n – Use the following command to disable the identity property on the column:
\n “`sql
\n SET IDENTITY_INSERT [table_name] ON;
\n “`
\n – Replace `[table_name]` with the actual name of the table containing the identity column.<\/p>\n

2. **Update the Value**
\n – Update the value of the identity column using a SQL UPDATE statement:
\n “`sql
\n UPDATE [table_name] SET [identity_column_name] = [new_value] WHERE [condition];
\n “`
\n – Replace `[table_name]`, `[identity_column_name]`, `[new_value]`, and `[condition]` with the appropriate values.<\/p>\n

3. **Enable the Identity Property**
\n – After updating the value, enable the identity property on the column again:
\n “`sql
\n SET IDENTITY_INSERT [table_name] OFF;
\n “`<\/p>\n

4. **Verify the Changes**
\n – Make sure to verify that the identity column value has been successfully updated by running a SELECT query:
\n “`sql
\n SELECT * FROM [table_name];
\n “`<\/p>\n

Changing the value of an identity column should be done carefully as it can affect the integrity of the data in the table. Always make sure to back up your data before making any changes.<\/p>\n

\n
\n

Table of Contents<\/p>\nToggle<\/span><\/path><\/svg><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n