我有一个MS SQL 2005数据库,其表 Test
,列 ID
. ID
是一个标识列 .
我在这个表中有行,所有行都有相应的ID自动递增值 .
现在我想更改此表中的每个ID,如下所示:
ID = ID + 1
但是当我这样做时,我收到一个错误:
无法更新标识列'ID' .
我试过这个:
ALTER TABLE Test NOCHECK CONSTRAINT ALL
set identity_insert ID ON
但这并没有解决问题 .
我需要将标识设置为此列,但我还需要不时更改值 . 所以我的问题是如何完成这项任务 .
13 回答
你需要
然后删除您的行并使用不同的标识重新插入它 .
完成插入后,不要忘记关闭identity_insert
IDENTITY
列值是不可变的 .但是,可以切换表元数据以删除
IDENTITY
属性,执行更新,然后切换回 .假设有以下结构
那你可以做
在SQL Server 2012中,可以使用
SEQUENCES
自动递增列,也可以更直接地更新通过SQL Server 2005管理器中的UI,更改列删除列的自动编号(标识)属性(通过右键单击选择表并选择“设计”) .
然后运行您的查询:
然后,将autonumber属性添加回列 .
首先,为此开启或关闭IDENTITY_INSERT的设置将不适用于您需要的内容(它用于插入新值,例如插入间隙) .
通过GUI执行操作只会创建一个临时表,将所有数据复制到没有标识字段的新表中,并重命名该表 .
这可以使用临时表来完成 .
这个想法
禁用约束(如果您的id被外键引用)
使用新id创建临时表
删除表格内容
将复制表中的数据复制回原始表
启用预先禁用的约束
SQL查询
假设您的
test
表有两个附加列(column2
和column3
),并且有两个表具有引用test
的外键,称为foreign_table1
和foreign_table2
(因为实际问题从未如此简单) .而已 .
DBCC CHECKIDENT('databasename.dbo.orders',RESEED,999)您可以使用此命令更改任何标识列号,并且您也可以从您想要的每个数字开始该字段编号 . 例如,在我的命令中,我要求从1000(999 1)希望这就够了......祝你好运
如果列不是PK,则始终可以使用递增的数字在表中创建新列,删除原始列,然后将新的列更改为旧的 .
好奇为什么你可能需要这样做...大多数我曾经不得不使用Identity列来回填数字而我最后使用了DBCC CHECKIDENT(tablename,RESEED,newnextnumber)
祝好运!
身份修改可能会失败,具体取决于许多因素,主要是围绕链接到id列的对象/关系 . 似乎数据库设计在这里是问题,因为id应该很少,如果有变化(我确定你有你的理由,并且正在改变) . 如果你真的需要不时更改id,我建议你创建一个新的虚拟id列,它不是你可以自己管理并从当前值生成的主键/自动编号 . 或者,如果您在允许身份插入时遇到问题,那么上面的Chrisotphers想法将是我的另一个建议 .
祝好运
PS它没有失败,因为它运行的顺序正在尝试将列表中的值更新为已存在于ID列表中的项目? grab 吸管,或许添加行数1,然后如果可行则减去行数:-S
如果您需要偶尔更改ID,最好不要使用标识列 . 过去,我们使用“计数器”表手动实现自动编号字段,该表跟踪每个表的下一个ID . IIRC我们之所以这样做,是因为标识列导致SQL2000中的数据库损坏,但能够更改ID偶尔会对测试有用 .
您可以插入具有修改值的新行,然后删除旧行行 . 以下示例更改ID与外键PersonId相同
首先保存所有ID并以编程方式将它们更改为您想要的值,然后将它们从数据库中删除,然后使用类似的东西再次插入它们:
对于批量插入使用:
来自file.csv的示例数据:
如果未将
identity_insert
设置为off,则会出现以下错误:我看到了一篇很好的文章,它帮助我在最后一刻...我试图在一个有一个标识列的表中插入几行但是做错了并且必须删除 . 删除行后,我的标识列发生了变化 . 我试图找到一种方法来更新插入的列但是 - 没有运气 . 所以,在google上搜索时发现了一个链接..
删除了错误插入的列
使用身份开启/关闭强制插入(解释如下)
http://beyondrelational.com/modules/2/blogs/28/posts/10337/sql-server-how-do-i-insert-an-explicit-value-into-an-identity-column-how-do-i-update-the-value-of-an.aspx
非常好的问题,首先我们需要 on 特定表的IDENTITY_INSERT,之后运行插入查询(必须指定列名) .
Note: 编辑标识列后,不要忘记 off IDENTITY_INSERT . 如果您还没有完成,则无法编辑任何其他表的标识列 .
http://allinworld99.blogspot.com/2016/07/how-to-edit-identity-field-in-sql.html