只有在使用列列表并且 IDENTITY_INSERT 为 ON SQL Server 时，才能为表中的标识列指定显式值

2022-01-31 00:00:00 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's answer 或 安多玛的回答.

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/Table and Database 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.

或

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

相关文章