问仅当使用列列表并且IDENTITY_INSERT位于SQL Server上时，才能为表中的标识列指定显式值

EN

摘要

SQL Server不允许您在标识列中插入显式值，除非您使用列列表。因此，您有以下选项：

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

或

1. 使tbl_A_archive中的identity列成为常规的非identity列:如果您的表是一个存档表，并且您总是为identity列指定显式的值，那么为什么还需要identity列呢？只需使用常规int即可。

有关解决方案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;

其中field1, field2, ...包含表中所有列的名称。如果希望自动生成列列表，可以查看Dave's answer或Andomar's answer。

有关解决方案2的详细信息

不幸的是，不可能只将标识int列的“类型”更改为非标识int列。基本上，您有以下选项：

• 如果存档表还不包含数据，请删除该列，然后添加一个没有标识的新列。

或

• 使用SQL Server Management Studio将存档表中标识列的Identity Specification/(Is Identity)属性设置为No。在后台，这将创建一个脚本来重新创建表并复制现有数据，因此，要执行此操作，还需要取消设置Tools/Options/Designers/Table and Database Designers/Prevent saving changes that require table re-creation.

或

使用此答案中描述的一种解决方法：Remove Identity from a column in a table

SET IDENTITY_INSERT tableA ON

您必须为INSERT语句创建一个列列表：

INSERT Into tableA ([id], [c2], [c3], [c4], [c5] ) 
SELECT [id], [c2], [c3], [c4], [c5] FROM tableB

不像"INSERT Into tableA SELECT ......“

SET IDENTITY_INSERT tableA OFF

如果您使用的是SQL Server Management Studio，则不必自己键入列列表-只需在对象资源管理器中右键单击该表，然后选择“将脚本表作为 -> SELECT to -> 新建查询编辑器窗口”。

如果不是这样，那么一个类似下面的查询应该会作为一个起点提供帮助：

SELECT SUBSTRING(
    (SELECT ', ' + QUOTENAME(COLUMN_NAME)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'tbl_A'
        ORDER BY ORDINAL_POSITION
        FOR XML path('')),
    3,
    200000);