设置

  IDENTITY_INSERT  

属性ON表格不会有任何区别，因为这只影响新记录而不影响现有记录。

如果您确实需要更改标识列的值，可以分两步完成。第一步是INSERT包含现有记录的相同信息的新记录，其中需要更新标识列，为这些新记录分配新ID。第二步是删除原始记录。



然后这是一个风险，因为你可能不想丢失你的数据，如果有任何参考完整性如外国其他表的键。然后你还需要删除那些表中的条目。

这是一个非常大的风险。但如果你这样做是为了一个简单的目的，那就试试吧。

  set   identity_insert  YourTable  ON  
  -   然后删除您的行并以不同的身份重新插入。 
  -   完成插入后，不要忘记关闭identity_insert  
 set   identity_insert  YourTable  OFF  

如果不仔细播放，则无法更新标识列 - 和在***的时候，这是一个非常非常糟糕的主意！

想一想：你可能还有其他表，其数据取决于这里的值，（可能会或可能不会被标记为外键） ，虽然他们应该），你可能有其他用户在更改数据库时访问数据库。



如果你觉得你需要一个行号，那么返回行号会更好，因为它会有没有差距，也不会造成任何数据问题。



如果你真的必须这样做，那么快速谷歌会告诉你如何 - 但我强烈反对它！ / BLOCKQUOTE>

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'

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

Setting the

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.

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

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!