表中标识列的显式值只能在使用列列表且 IDENTITY_INSERT 为 ON SQL Server 时指定 [英] An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON SQL Server

问题描述

我正在尝试执行此查询

INSERT INTO dbo.tbl_A_archive
  SELECT *
  FROM SERVER0031.DB.dbo.tbl_A

但即使我跑了

set identity_insert dbo.tbl_A_archive on

我收到此错误消息

只有在使用列列表且 IDENTITY_INSERT 为 ON 时，才能为表 'dbo.tbl_A_archive' 中的标识列指定显式值.

An explicit value for the identity column in table 'dbo.tbl_A_archive' can only be specified when a column list is used and IDENTITY_INSERT is ON.

tbl_A 是一个巨大的行和宽的表格，即它有很多列.我不想手动输入所有列.我怎样才能让它发挥作用?

tbl_A is a huge table in rows and width, i.e. it has a LOT of columns. I do not want to have to type all the columns out manually. How can I get this to work?

推荐答案

总结

除非您使用列列表，否则 SQL Server 不允许您在标识列中插入显式值.因此，您有以下选择:

SQL Server won't let you insert an explicit value in an identity column unless you use a column list. Thus, you have the following options:

1. 制作列列表(手动或使用工具，见下文)

或

1. 使 tbl_A_archive 中的标识列成为常规的非标识 列:如果您的表是存档表并且您始终为标识列指定显式值，为什么你甚至需要一个身份栏?只需使用常规 int 即可.

1. make the identity column in tbl_A_archive a regular, non-identity column: If your table is an archive table and you always specify an explicit value for the identity column, why do you even need an identity column? Just use a regular int instead.

---

解决方案 1 的详细信息

代替

SET IDENTITY_INSERT archive_table ON;

INSERT INTO archive_table
  SELECT *
  FROM source_table;

SET IDENTITY_INSERT archive_table OFF;

你需要写

SET IDENTITY_INSERT archive_table ON;

INSERT INTO archive_table (field1, field2, ...)
  SELECT field1, field2, ...
  FROM source_table;

SET IDENTITY_INSERT archive_table OFF;

with field1, field2, ... 包含表中所有列的名称.如果您想自动生成该列列表，请查看 Dave 的答案 或 Andomar 的回答.

with field1, field2, ... containing the names of all columns in your tables. If you want to auto-generate that list of columns, have a look at Dave's answer or Andomar's answer.

解决方案 2 的详细信息

不幸的是，不能仅仅更改类型"将标识 int 列转换为非标识 int 列.基本上，您有以下选择:

Unfortunately, it is not possible to just "change the type" of an identity int column to a non-identity int column. Basically, you have the following options:

• 如果存档表尚不包含数据，请删除该列并添加一个没有标识的新列.

或

• 使用 SQL Server Management Studio 将存档表中标识列的 Identity Specification/(Is Identity) 属性设置为 No.在幕后，这将创建一个脚本来重新创建表并复制现有数据，因此，为此，您还需要取消设置 Tools/Options/Designers/表和数据库设计器/防止保存需要重新创建表的更改.

• Use SQL Server Management Studio to set the Identity Specification/(Is Identity) property of the identity column in your archive table to No. Behind the scenes, this will create a script to re-create the table and copy existing data, so, to do that, you will also need to unset Tools/Options/Designers/Table and Database Designers/Prevent saving changes that require table re-creation.

或

• 使用此答案中描述的解决方法之一:从表中的列中删除标识

这篇关于表中标识列的显式值只能在使用列列表且 IDENTITY_INSERT 为 ON SQL Server 时指定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！