我有SQL Server数据库,我想改变标识列,因为它开始了 有一个大数字10010,它与另一个表相关,现在我有200条记录,我想在记录增加之前修复这个问题。

更改或重置该列的最佳方法是什么?


当前回答

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不允许你在没有指定列名的情况下进行插入

其他回答

你需要

set identity_insert YourTable ON

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

插入完成后,不要忘记关闭identity_insert

set identity_insert YourTable OFF
--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

您还可以使用SET IDENTITY INSERT来允许您将值插入到标识列中。

例子:

SET IDENTITY_INSERT dbo.Tool ON
GO

然后你可以在单位列中插入你需要的值。

将您的表复制到没有标识列的新表。

    select columns into newtable from yourtable

用new seed添加一个标识列到newtable,并将其作为主键

    ALTER TABLE tableName ADD id MEDIUMINT NOT NULL AUTO_INCREMENT KEY
DBCC CHECKIDENT(table_name, RESEED, value)

Table_name =给出要重置的表的值

Value =初始值为0,以1开始单位列