How to update identity column value in SQL SERVER?

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

Hello,

How to update identity column value in SQL SERVER?
I am using this query for update identity column value in sql server.

SQL

set IDENTITY_INSERT tableSalesQuotation ON
update tableSalesQuotation
set
SNO=SubQuery.SNO
from
(SELECT Row_Number() OVER (ORDER BY SNO) as SNO
FROM tableSalesQuotation
) SubQuery
set IDENTITY_INSERT tableSalesQuotation OFF

but it's getting error.
Cannot update identity column 'SNO'

I have to update existing Identity Column Value, it's a application need.

please help me.

Thanks in Advance.

Ankit Agarwal
Software Engineer

Posted 11-Apr-14 20:48pm

ankit.agg1984

Updated 22-Sep-19 11:45am

Add a Solution

2 solutions

Solution 2

If there is no dependency between the identity value of your table and other tables, you can easily change the new to be inserted value using this code:
DBCC CHECKIDENT ( [YourTabl], RESEED ,[new desired new value])

Also, if you need to eliminate holes in your identity values, you can
1 convert the identity field into an integer
2 edit the values of the identity field (now an integer), using forn instance excel incremental rows)
3 make sure there are no duplicate values
4 convert your column into an identity field again

Hope it helps
Ramon Baiges

Posted 22-Sep-19 11:45am

Member 14600529

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2014-04-11T21:10:00

It's a very, very poor idea.
The whole idea of an IDENTITY field is that the value is unique, not that it is sequential - which means that as rows are added and removed the values are not reused, but "holes" appear where values used to be. If you update them to give sequential values, then in 30 seconds there could be holes again!

It is also a dangerous thing to do; of you have other tables that refer to this value, you could well destroy your data integrity by doing this. And... What if you have multiple users? When data like this is updated, it can cause some horrible problems...

Please, don't do it. If you need sequential numbers, then use ROW_NUMBER to provide it, but don't update IDENTITY value to give sequential results - it is seriously not what it is there for.

If you still think you really, really, should do this, then a simple Google will find you how to do it... But seriously, if you have to, then there is a problem with your design!