设置identity_insert CHANGE ID FIELD [英] Set identity_insert CHANGE ID FIELD

问题描述

set identity_insert [TESTTABEL] ON

//Messages ------------------------
//Command(s) completed successfully.

UPDATE [dbo].[TESTTABEL]
SET ID = 1 WHERE ID = 10

//Messages ------------------------ HELP!!
//Msg 8102, Level 16, State 1, Line 4
//Cannot update identity column 'ID'.

set identity_insert [TESTTABEL] OFF

//Messages ------------------------
//return >> Command(s) completed successfully.

推荐答案

正如Chill60所说，当identity_insert为ON时，您无法更改IDENTITY值，因为它告诉SQL来控制值。由于它处于控制状态，它会提供新值并在您尝试指定时提出抱怨。

更改IDENTITY值通常表明您做错了什么：在您的情况下，您似乎是试图重用空值 - 但这不会起作用，因为SQL将10视为已使用并将提供11作为下一个值 - 给你另一个洞！

如果你想要连续数字，然后不使用IDENTITY - 这是唯一值，其中确切的数字无关紧要，通常只用于绝对识别表之间的关系或行数据的更改行。相反，使用 ROW_NUMBER（Transact-SQL） [ ^ ]将是顺序（但在查询完成后不会唯一地标识一行）。

As Chill60 says, you can't change IDENTITY values when identity_insert is ON, because that tells SQL to take control of the values. Since it's in control, it supplies the new value and complains when you try to specify it.
And changing IDENTITY values is normally a sign that you are doing something wrong: in your case, you appear to be trying to "reuse" empty values - but that won't work as SQL considers 10 as "used" and will supply 11 as the next value - giving you another hole!
If you want to have contiguous numbers, then don't use IDENTITY - that's for unique values, where the exact number doesn't matter and is generally used just to absolutely identify a row for relationships between tables or for changes to the row data. Instead, use ROW_NUMBER (Transact-SQL)[^] which will be sequential (but will not uniquely identify a line once the query is complete).

我发现这篇文章似乎可以满足您的要求 - sql server - 如何以编程方式更改标识列值？ - 堆栈溢出 [ ^ ]。请参阅Martin Smith的解决方案（不是本文中接受的答案） - 他提供了一种方法，您可以导入数据，保留原始值，然后更改表格，随后自动增加id字段。

I found this post which would appear to address your requirements - sql server - How to change identity column values programmatically? - Stack Overflow[^]. See the solution from Martin Smith (not the accepted answer on this post) - he offers a method where you can import the data, retaining the original values and then change the table to subsequently increment the id field automatically.

这篇关于设置identity_insert CHANGE ID FIELD的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！