SQL 当 IDENTITY_INSERT 设置为 OFF 时，无法为表 'table' 中的标识列插入显式值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1334012/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Cannot insert explicit value for identity column in table 'table' when IDENTITY_INSERT is set to OFF
提问by pjp
I have the below error when I execute the following script. What is the error about, and how it can be resolved?
执行以下脚本时出现以下错误。错误是什么，如何解决？
Insert table(OperationID,OpDescription,FilterID)
values (20,'Hierachy Update',1)
Error:
错误：
Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'table' when IDENTITY_INSERT is set to OFF.
服务器：消息 544，级别 16，状态 1，第 1 行
当 IDENTITY_INSERT 设置为 OFF 时，无法为表 'table' 中的标识列插入显式值。
回答by pjp
You're inserting values for OperationIdthat is an identity column.
您要插入的值OperationId是标识列。
You can turn on identity insert on the table like this so that you can specify your own identity values.
您可以像这样在表上打开身份插入，以便您可以指定自己的身份值。
SET IDENTITY_INSERT Table1 ON
INSERT INTO Table1
/*Note the column list is REQUIRED here, not optional*/
(OperationID,
OpDescription,
FilterID)
VALUES (20,
'Hierachy Update',
1)
SET IDENTITY_INSERT Table1 OFF
回答by Wael Dalloul
don't put value to OperationID because it will be automatically generated. try this:
不要给 OperationID 赋值，因为它会自动生成。尝试这个：
Insert table(OpDescription,FilterID) values ('Hierachy Update',1)
回答by Umang Patwa
Simply If you getting this error on SQL server then run this query-
如果您在 SQL 服务器上收到此错误，请运行此查询-
SET IDENTITY_INSERT tableName ON
this is working only single table of databsee.g If table name is student then query look like this SET IDENTITY_INSERT student ON
这仅适用于单个数据库表，例如，如果表名是学生，则查询如下所示 SET IDENTITY_INSERT student ON
If you getting this error on your web application or you using entity framework then first run this query on SQL server and Update your entity model (
.edmx file)and build your projectand this error will be resolved
如果您在 Web 应用程序或使用实体框架上收到此错误，则首先在 SQL 服务器上运行此查询并更新您的实体模型 (
.edmx file)并构建您的项目，此错误将得到解决
回答by HLGEM
Be very wary of setting IDENTITY_INSERT to ON. This is a poor practice unless the database is in maintenance mode and set to single user. This affects not only your insert, but those of anyone else trying to access the table.
将 IDENTITY_INSERT 设置为 ON 时要非常小心。这是一种糟糕的做法，除非数据库处于维护模式并设置为单用户。这不仅会影响您的插入，还会影响其他任何试图访问该表的人的插入。
Why are you trying to put a value into an identity field?
为什么要尝试将值放入标识字段？
回答by QA Specialist
There are basically 2 different ways to INSERT records without having an error:
基本上有两种不同的方法可以插入记录而不会出错：
1) When the IDENTITY_INSERT is set OFF. The PRIMARY KEY "ID" MUST NOT BE PRESENT
1) 当 IDENTITY_INSERT 设置为 OFF 时。主键“ID”不得存在
2) When the IDENTITY_INSERT is set ON. The PRIMARY KEY "ID" MUST BE PRESENT
2) 当 IDENTITY_INSERT 设置为 ON 时。必须存在主键“ID”
As per the following example from the same Table created with an IDENTITY PRIMARY KEY:
根据使用 IDENTITY PRIMARY KEY 创建的同一个表中的以下示例：
CREATE TABLE [dbo].[Persons] (
ID INT IDENTITY(1,1) PRIMARY KEY,
LastName VARCHAR(40) NOT NULL,
FirstName VARCHAR(40)
);
1) In the first example, you can insert new records into the table without getting an error when the IDENTITY_INSERT is OFF. The PRIMARY KEY "ID" MUST NOT BE PRESENTfrom the "INSERT INTO" Statements and a unique ID value will be added automatically:. If the ID is present from the INSERT in this case, you will get the error "Cannot insert explicit value for identify column in table..."
1) 在第一个示例中，当 IDENTITY_INSERT 为 OFF 时，您可以将新记录插入表中而不会出错。PRIMARY KEY “ID”不得出现在“INSERT INTO”语句中，并且会自动添加唯一的 ID 值：。如果在这种情况下 ID 来自 INSERT，您将收到错误“无法为表中的标识列插入显式值...”
SET IDENTITY_INSERT [dbo].[Persons] OFF;
INSERT INTO [dbo].[Persons] (FirstName,LastName)
VALUES ('JANE','DOE');
INSERT INTO Persons (FirstName,LastName)
VALUES ('JOE','BROWN');
OUTPUT of TABLE [dbo].[Persons] will be:
表 [dbo].[Persons] 的输出将是：
ID LastName FirstName
1 DOE Jane
2 BROWN JOE
2) In the Second example, you can insert new records into the table without getting an error when the IDENTITY_INSERT is ON. The PRIMARY KEY "ID" MUST BE PRESENTfrom the "INSERT INTO" Statements as long as the ID value does not already exist: If the ID is NOT present from the INSERT in this case, you will get the error "Explicit value must be specified for identity column table..."
2) 在第二个示例中，当 IDENTITY_INSERT 为 ON 时，您可以将新记录插入表中而不会出错。只要 ID 值不存在，就必须从“INSERT INTO”语句中存在主键“ID”：如果在这种情况下从 INSERT 中不存在 ID，您将收到错误“显式值必须是为标识列表指定...”
SET IDENTITY_INSERT [dbo].[Persons] ON;
INSERT INTO [dbo].[Persons] (ID,FirstName,LastName)
VALUES (5,'JOHN','WHITE');
INSERT INTO [dbo].[Persons] (ID,FirstName,LastName)
VALUES (3,'Hyman','BLACK');
OUTPUT of TABLE [dbo].[Persons] will be:
表 [dbo].[Persons] 的输出将是：
ID LastName FirstName
1 DOE Jane
2 BROWN JOE
3 BLACK Hyman
5 WHITE JOHN
回答by user902490
In your entity for that table, add the DatabaseGeneratedattribute above the column for which identity insert is set:
在该表的实体中，在DatabaseGenerated设置了标识插入的列上方添加属性：
Example:
例子：
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int TaskId { get; set; }
回答by user902490
you can simply use This statement for example if your table name is School. Before insertion make sure identity_insert is set to ONand after insert query turn identity_insert OFF
例如，如果您的表名是School ，则可以简单地使用 This 语句。插入前确保 identity_insert 设置为ON，插入查询后将 identity_insert 设置为 OFF
SET IDENTITY_INSERT School ON
/*
insert query
enter code here
*/
SET IDENTITY_INSERT School OFF
回答by iowatiger08
If you are using liquibase to update your SQL Server, you are likely trying to insert a record key into an autoIncrement field. By removing the column from the insert, your script should run.
如果您正在使用 liquibase 更新您的 SQL Server，您可能会尝试将记录键插入到 autoIncrement 字段中。通过从插入中删除列，您的脚本应该运行。
<changeSet id="CREATE_GROUP_TABLE" >
<createTable tableName="GROUP_D">
<column name="GROUP_ID" type="INTEGER" autoIncrement="true">
<constraints primaryKey="true"/>
</column>
</createTable>
</changeSet>
<changeSet id="INSERT_UNKNOWN_GROUP" >
<insert tableName="GROUP_D">
<column name="GROUP_ID" valueNumeric="-1"/>
...
</insert>
</changeSet>
回答by Onkar_M18
There is pre-mentioned OperationId in your query which should not be there as it is auto increamented
您的查询中有预先提到的 OperationId 不应该存在，因为它是自动增加的
Insert table(OperationID,OpDescription,FilterID)
values (20,'Hierachy Update',1)
so your query will be
所以你的查询将是
Insert table(OpDescription,FilterID)
values ('Hierachy Update',1)
回答by Salem BeeJay Kosemani
Another situation is to check that the Primary Key is the same nameas with your classes where the only difference is that your primary key has an 'ID' appended to it or to specify [Key] on primary keys that are not related to how the class is named.
另一种情况是检查主键是否与您的类同名，唯一的区别是您的主键附加了一个“ID”，或者在与主键无关的主键上指定 [Key]类被命名。
