How to update Identity Column from ROW_NUMBER in sql server 2008?

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

Hello,

How to update Identity Column from ROW_NUMBER in sql server 2008?

I am using this query for update but it's getting error:-
Cannot update identity column 'SNO'

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

please help me.

Thanks in Advance.

Ankit Agarwal
Software Engineer

Posted 11-Apr-14 0:43am

ankit.agg1984

Updated 11-Apr-14 1:52am

v4

Add a Solution

Comments

Mike Meinz 11-Apr-14 7:20am

Nobody does this! If you think you need to do this, please re-evaluate your data and application design.

2 solutions

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)

Schatak · Answer 1 · 2014-04-11T01:17:00

Setting the

SQL

IDENTITY_INSERT

property of the table to ON will not make any difference as this only affects new records and not existing ones.
If you really need to change the values of an identity column, this can be accomplished in 2 steps. The first step is to INSERT new records containing the same information of the existing records whose identity column needs to be updated assigning the new IDs for these new records. The second step is deleting the original records.

but then that's a risk because you may not want to loose your data, and also if there is any referential integrity like Foreign keys to other tables. Then you also need to remove the entries from that tables.
So quite a risk. but if you are doing it in a simple purpose then try this.

SQL

set identity_insert YourTable ON
--Then delete your row and reinsert it with different identity.
--Once you have done the insert don't forget to turn identity_insert off
set identity_insert YourTable OFF

OriginalGriff · Answer 2 · 2014-04-11T01:07:00

You can't update Identity columns without some careful playing - and it's a very, very bad idea at the best of times!
Think about it: you may well have other tables whose data depends on the value here, (which may or may not be marked as foreign keys, though they should be) and you may have other users accessing the database while you are changing it.

It is a much, much better idea to return a row number instead if you feel you need one, as that will have no gaps and will not cause any data problems.

If you really must do it, then a quick google will show you how - but I strongly advise against it!

How to update Identity Column from ROW_NUMBER in sql server 2008?

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0