我正在尝试执行这个查询
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' 中的标识列指定显式值。
tbl_A 是一个非常大的表，它有很多行和列。 我不想手动输入所有列。 有什么方法可以让这个工作？
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 中，除非使用列列表，否则无法在标识列中插入显式值。因此，您有以下几个选项：
或者
tbl_A_archive 中的标识列更改为常规的、非标识列：如果您的表是一个归档表，并且您总是为标识列指定显式值，那么您甚至不需要标识列，只需使用普通的 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列是不可能的。基本上，您有以下几个选项：
或者
Identity Specification/(Is Identity)属性设置为No。在幕后，这将创建一个脚本来重新创建表格和复制现有数据，因此，为了实现这一点，您还需要取消设置 Tools/Options/Designers/Table and Database Designers/Prevent saving changes that require table re-creation。或者
如果你正在使用SQL Server Management Studio，你不必自己输入列列表 - 只需右键单击对象资源管理器中的表格，并选择生成脚本 -> 选择 -> 新建查询编辑器窗口。
如果没有使用SQL Server Management Studio，则类似于此查询的查询可作为起点：
SELECT SUBSTRING(
(SELECT ', ' + QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tbl_A'
ORDER BY ORDINAL_POSITION
FOR XML path('')),
3,
200000);
同意Heinzi的答案。对于第一和第二个选项，这里有一个查询语句可以生成表中逗号分隔的列列表：
select name + ', ' as [text()]
from sys.columns
where object_id = object_id('YourTable')
for xml path('')
对于大型表格，这可以节省很多打字工作 :)
SET IDENTITY_INSERT tbl_A_archive ON
--Your inserts here
SET IDENTITY_INSERT tbl_A_archive OFF
最后，如果您需要身份列按原样工作，则始终可以运行存储过程。
sp_columns tbl_A_archive
这将返回表中的所有列，然后您可以将它们剪切并粘贴到您的查询中。(这几乎总是比使用 * 更好)
对于 SQL 语句，您还必须指定列列表。例如：
INSERT INTO tbl (idcol1,col2) VALUES ( value1,value2)
取代
INSERT INTO tbl VALUES ( value1,value2)
SET IDENTITY_INSERT customers ON
GO
insert into dbo.tbl_A_archive(id, ...)
SELECT Id, ...
FROM SERVER0031.DB.dbo.tbl_A
2)
SET IDENTITY_INSERT customers ON
GO
insert into dbo.tbl_A_archive(id, ...)
VALUES(@Id,....)
SET IDENTITY_INSERT DuplicateTable ON
INSERT Into DuplicateTable ([IdentityColumn], [Column2], [Column3], [Column4] )
SELECT [IdentityColumn], [Column2], [Column3], [Column4] FROM MainTable
SET IDENTITY_INSERT DuplicateTable OFF
SELECT column_name + ','
FROM information_schema.columns
WHERE table_name = 'TableName'
for xml path('')
SELECT column_name + ','
FROM information_schema.columns
WHERE table_name = 'YourTable'
2) 如果您使用SQL Server SSMS，这可能是创建列最简单的方法。
1) 在对象资源管理器中找到表格，并单击表格名称左侧的“+”或双击表格名称以打开子列表。
2) 将列子文件夹拖到主查询区域，它将自动将整个列列表粘贴到您的代码中。
SET IDENTITY_INSERT tableA ON
INSERT Into tableA ([id], [c2], [c3], [c4], [c5] )
SELECT [id], [c2], [c3], [c4], [c5] FROM tableB
不是这样的
INSERT INTO tableA
SELECT * FROM tableB
SET IDENTITY_INSERT tableA OFF