打开 IDENTITY_INSERT 不起作用

Question

我想从一个叫做MyDBQA的数据库中复制一张表格Equipment到我们的测试数据库MyDB。这个表格包含了一个自增列作为主键（int类型，非空）。

但是我出现了一个错误：

Msg 8101, Level 16, State 1, Line 2
当使用列清单并开启IDENTITY_INSERT时，只能在表'MyDB.dbo.Equipment'中指定自增列的特殊值。

我的脚本：

SET IDENTITY_INSERT [MyDB].[dbo].[Equipment] ON
INSERT INTO [MyDB].[dbo].[Equipment]  SELECT * FROM [MyDBQA].[dbo].[Equipment]
SET IDENTITY_INSERT [MyDB].[dbo].[Equipment] OFF

- user1108948

---

当使用列列表 AND ... - Damien_The_Unbeliever

可能是重复的问题：在SQL Server中，只有在使用列列表并且IDENTITY_INSERT为ON时才能指定表中标识列的显式值。 - Rousonur Jaman

请查看此处的答案：链接 - Rousonur Jaman

Answer 1

你可能只是缺少列列表，就像消息所说的那样

SET IDENTITY_INSERT [MyDB].[dbo].[Equipment] ON

INSERT INTO [MyDB].[dbo].[Equipment]
            (COL1,
             COL2)
SELECT COL1,
       COL2
FROM   [MyDBQA].[dbo].[Equipment]

SET IDENTITY_INSERT [MyDB].[dbo].[Equipment] OFF 

- Michal Klouda

Answer 2

错误信息中相关的部分为

...when a column list is used...

您没有使用列清单，而是使用了SELECT *。请改为使用列清单：

SET IDENTITY_INSERT [MyDB].[dbo].[Equipment] ON 

INSERT INTO [MyDB].[dbo].[Equipment] (Col1, Col2, ...)
SELECT Col1, Col2, ... FROM [MyDBQA].[dbo].[Equipment] 

SET IDENTITY_INSERT [MyDB].[dbo].[Equipment] OFF 

- Heinzi

---

Col1 可以作为标识列吗？ - user1108948

是的。 - Heinzi

Answer 3

在VB代码中，当尝试提交一个INSERT查询时，你必须在同一个"executenonquery"中提交双重查询，例如：

sqlQuery = "SET IDENTITY_INSERT dbo.TheTable ON; INSERT INTO dbo.TheTable (Col1, COl2) VALUES (Val1, Val2); SET IDENTITY_INSERT dbo.TheTable OFF;"

我使用了分号;代替了GO。

对我来说运行良好。虽然有点晚但效率高！

- user4272270

---

伙计，您甚至不知道您对我有多大帮助！这不是VB，而是Node.js。我尝试过事务、使用GO，但都没有帮上忙...感谢在寻找三个小时后终于帮助我！ - CherryNerd

这也是我的问题的答案！非常感谢你。 - desap

Answer 4

这里是微软关于使用SET IDENTITY_INSERT的说明，对于像我一样在尝试重新创建已删除记录并保留原始标识列值时发现此帖子的其他人可能会有所帮助。

要重新创建具有原始标识列值的已删除记录： http://msdn.microsoft.com/en-us/library/aa259221(v=sql.80).aspx

- user2574095