如何在SQL Server中更新身份列？

Question

我有一个SQL Server数据库，想要更改自增列的起始值，因为它从 10010 开始，并且与另一个表相关联。现在我有200条记录，希望在记录数量增加之前解决这个问题。

最好的方式是如何更改或重置此列？

- Abdulsalam Elsharif

Answer 1

无法更新标识列。

与其他列可以使用更新语句不同，SQL Server 不允许更新标识列。

尽管有一些替代方法可实现类似的需求。

• 当需要为新记录更新标识列值时

使用DBCC CHECKIDENT，检查表的当前标识值，如果需要，则更改标识值。

DBCC CHECKIDENT('tableName', RESEED, NEW_RESEED_VALUE)

• 当需要更新现有记录的标识列值时

使用IDENTITY_INSERT，它允许将明确的值插入到表的标识列中。

SET IDENTITY_INSERT YourTable {ON|OFF}

例子：

-- Set Identity insert on so that value can be inserted into this column
SET IDENTITY_INSERT YourTable ON
GO
-- Insert the record which you want to update with new value in the identity column
INSERT INTO YourTable(IdentityCol, otherCol) VALUES(13,'myValue')
GO
-- Delete the old row of which you have inserted a copy (above) (make sure about FK's)
DELETE FROM YourTable WHERE ID=3
GO
--Now set the idenetity_insert OFF to back to the previous track
SET IDENTITY_INSERT YourTable OFF

- Sachin

---

DBCC重置下一个新记录，但我现在想更改现有的记录。 - Abdulsalam Elsharif

@sachin，这不是更新现有的自增长标识符，而是手动插入。 - Phill Greggan

@PhillGreggan 是的，这是实现此目标的最佳解决方案。您无法像更新普通列一样更新 Identity 列。 - Sachin

这个被接受的回答并没有回答问题，问题是如何更新一个自增列（例如UPDATE YourTable SET IdentityCol = 13）。SET IDENTITY_INSERT YourTable ON 只允许插入而不是更新。 - Ian Boyd

我认为回答一个“如何…”的问题时用“你不能…”是没有用的，或者至少不要将其投票为最佳答案，因为它并没有解决问题。@Luv的回复虽然不是一项容易的操作或设计建议，但确实解决了问题。 - Lucky Brain

Answer 2

如果我理解您的问题正确，您想要做类似于下面的事情：

update table
set identity_column_name = some value

让我告诉你，这不是一个简单的过程，也不建议使用它，因为可能会有一些与之关联的外键。

但以下是操作步骤，请先备份表格：

步骤1- 选择表格的设计视图

步骤2- 关闭自增列

现在您可以使用update查询。

完成后请重复步骤1和步骤2并打开自增列。

参考资料

- Prahalad Gaggar

---

我有另一个与这个表相关的表，我认为我做不到。 - Abdulsalam Elsharif

因此，该声明“不建议” :)。 - Prahalad Gaggar

@AbdusalamElsherif，但您问如何更改标识列。 - paparazzo

这可能有效。但对我来说并没有，我猜是因为其他表与我想要取消标识插入的表中的列有外键关联。真让人恼火。 - clamum

看起来如果你已经有数据在表中，它会报错并说"需要删除并重新创建表格"。 - user2040021

Answer 3

你需要

set identity_insert YourTable ON

然后删除您的行并使用不同的标识重新插入它。

一旦完成插入，请不要忘记关闭identity_insert。

set identity_insert YourTable OFF

- R S P

---

这比关闭列的身份验证要容易和安全得多！ - Protector one

这样更安全，对于少量记录来说也很容易，但并不能完全回答问题。关闭标识列虽然更危险，有时也不可能，但可以允许您更新标识列。 - Matthew Hudson

Answer 4

--before running this make sure Foreign key constraints have been removed that reference the ID. 

--set table to allow identity to be inserted
SET IDENTITY_INSERT yourTable ON;
GO
--insert everything into a temp table
SELECT * 
INTO #tmpYourTable
FROM yourTable

--clear your table
DELETE FROM yourTable
--insert back all the values with the updated ID column
INSERT INTO yourTable (IDCol, OtherCols)
SELECT ID+1 as updatedID --put any other update logic to the ID here
, OtherCols FROM #tmpYourTable
--drop the temp table
DROP TABLE #tmpYourTable
--put identity back to normal
SET IDENTITY_INSERT yourTable OFF;
GO

- kuklei

---

如果表非常大，执行删除操作可能会很耗时，这时建议使用“截断表（Truncate Table）”而非删除操作来清空表。截断表的速度非常快。但请注意，由于其不记录日志，所以一旦执行了截断操作就无法撤回。 - kuklei

@kuklei- 截断被记录了。这是一个常见的误解。截断操作在页面级别上运行，并记录它删除的页面列表。这就是为什么它如此快速的原因。DELETE是基于行的，如果涉及到FK，则需要使用它，截断无法处理它们并会出现错误。当然，在任何情况下，您都需要使用完整恢复模式（而不是简单模式）并进行日志备份。可以将其还原到截断操作之前。无论模型如何，您也可以尝试一下... BEGIN TRAN TRUNCATE table MyTable ROLLBACK。您的数据仍将存在。 - ripvlan

Answer 5

尝试使用DBCC CHECKIDENT命令：

DBCC CHECKIDENT ('YourTable', RESEED, 1);

- Darren

---

这不允许更新标识列。该语句仅重置标识值，用于插入新记录时使用，并不更改任何记录。 - Suncat2000

Answer 6

SET IDENTITY_INSERT dbo.TableName ON
INSERT INTO dbo.TableName 
(
    TableId, ColumnName1, ColumnName2, ColumnName3
)
VALUES
(
    TableId_Value, ColumnName1_Value, ColumnName2_Value, ColumnName3_Value
)

SET IDENTITY_INSERT dbo.TableName OFF

使用Identity_Insert时，不要忘记包括列名，因为SQL不允许您在没有指定列名的情况下进行插入。

- befree2j

Answer 7

我曾看到过几种方法来完成这个任务，但在我看来最好且更快的方法如下:

身份列有一个计数器，它不一定与已注册的列相同。您可以使用以下SQL命令查看此计数器的值:

DBCC CHECKIDENT('tableName', NORESEED);

如果您想编辑身份列，那么您将无法进行操作，但我建议在重置计数器后创建一个新记录，以达到您所需的编号。要重置计数器，请使用以下命令：

DBCC CHECKIDENT('tableName', RESEED, desiredNumber);

- Jose Manuel Lepe

Answer 8

我曾经遇到过类似的问题，我需要更新一些ID。我所做的是（我需要将它们增加10k）：

set identity_insert YourTable ON

INSERT INTO YourTable 
   ([ID]
   ,[something1]
   ,[something2]
   ,[something3])
SELECT 
   ([ID] + 10000)
   ,[something1]
   ,[something2]
   ,[something3]) 
FROM YourTable
WHERE something1 = 'needs updeted id' 
   AND something2 = 'some other condition'

set identity_insert YourTable OFF

DELETE FROM YourTable 
WHERE ID >= 'your old ID From' 
   AND ID <= 'Your old ID To' 

就是这样。希望您理解这个逻辑，在我这里还涉及到与其他表的PK-FK键连接，这意味着在从'YourTable'中删除原始行之前，我必须先更新它们。

我知道已经有答案了，我只是想留下SQL查询作为示例。

- Emilis Vadopalas

Answer 9

您还可以使用SET IDENTITY INSERT允许您向标识列插入值。

示例：

SET IDENTITY_INSERT dbo.Tool ON
GO

然后，您可以将所需的值插入到标识列中。

- DaveShaw

Answer 10

DBCC CHECKIDENT(table_name, RESEED, value)

= 指定需要重置值的表名 = 初始值为0，用于将标识列从1开始

- Yasmeen Ansari