设置 IDENTITY_INSERT ON 不起作用 [英] Set IDENTITY_INSERT ON is not working

问题描述

我想将一个表 Equipment 从一个数据库 MyDBQA 复制到我们的测试数据库 MyDB.表中有一个标识列是主键(int，not null).

I want to copy a table Equipment from one database MyDBQA to our test database MyDB. There is an identity column in the table which is the primary key (int, not null).

但是我遇到了一个错误:

But I got an error:

消息 8101，级别 16，状态 1，第 2 行
表 'MyDB.dbo.Equipment' 中标识列的显式值只能在使用列列表时指定并且 IDENTITY_INSERT 已开启.

Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'MyDB.dbo.Equipment' can only be specified when a column list is used and IDENTITY_INSERT is ON.

我的脚本:

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

推荐答案

正如消息所说，您可能只是缺少列列表

You might be just missing the column list, as the message says

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 

这篇关于设置 IDENTITY_INSERT ON 不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！