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

总结

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

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

或

2. 使tbl_A_archive中的标识列成为常规的non-identity列:如果你的表是一个存档表，并且你总是为标识列指定一个显式的值，为什么你甚至需要标识列?只需要使用常规int型就可以了。

方案一详细说明

而不是

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, ...包含表中所有列的名称。如果你想自动生成列列表，可以参考戴夫的回答或Andomar的回答。

方案二详细说明

不幸的是，仅仅“改变类型”是不可能的。将单位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。

或

• 使用这个答案中描述的一个变通方法:从表中的列中删除Identity

如果“归档”表是您的主表的精确副本，那么我只是建议您删除id是标识列的事实。这样你就可以插入它们了。

或者，您可以使用以下语句允许和禁止对表进行标识插入

SET IDENTITY_INSERT tbl_A_archive ON
--Your inserts here
SET IDENTITY_INSERT tbl_A_archive OFF

最后，如果您需要标识列按原样工作，那么您总是可以运行存储的过程。

sp_columns tbl_A_archive

这将返回表中的所有列，然后您可以将其剪切并粘贴到您的查询中。(这几乎总是比使用*更好)

如果你正在使用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);

同意海因茨的回答。对于第一个第二个选项，下面是一个查询，它在表中生成一个逗号分隔的列列表:

select name + ', ' as [text()]
from sys.columns
where object_id = object_id('YourTable')
for xml path('')

对于大的表格，这可以节省大量的打字工作:)

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

两者都可以工作，但如果使用#1仍然会出错，那么就使用#2

1）

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,....)

为了将所有列名填充到这个问题的解决方案的Select语句的逗号分隔列表中，我使用了以下选项，因为它们比这里的大多数响应更简洁。然而，这里的大多数回答仍然是完全可以接受的。

1）

SELECT column_name + ','
FROM   information_schema.columns
WHERE  table_name = 'YourTable'

1)在对象资源管理器中打开表，单击表名左侧的“+”或双击表名打开子列表。

2)将列子文件夹拖到主查询区，它将为您自动生成整个列列表。

如果存在Identity列，则必须指定要插入的列名。 因此，命令如下所示:

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('')

(删除最后一个逗号('，'))只需复制过去的列名。

对于SQL语句，还必须指定列列表。如。

INSERT INTO tbl (idcol1,col2) VALUES ( value1,value2)

而不是

INSERT INTO tbl VALUES ( value1,value2)

这应该有用。我刚碰到你的问题

SET IDENTITY_INSERT dbo.tbl_A_archive ON;
INSERT INTO     dbo.tbl_A_archive (IdColumn,OtherColumn1,OtherColumn2,...)
SELECT  *
FROM        SERVER0031.DB.dbo.tbl_A;
SET IDENTITY_INSERT dbo.tbl_A_archive OFF;

我添加了USE，因为我要在实例之间切换。

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

此代码片段显示当标识主键列为ON时如何插入到表中。

SET IDENTITY_INSERT [dbo].[Roles] ON
GO
insert into Roles (Id,Name) values(1,'Admin')
GO
insert into Roles (Id,Name) values(2,'User')
GO
SET IDENTITY_INSERT [dbo].[Roles] OFF
GO

如果您想通过存储过程将值从一个表插入到另一个表。我使用了这和这，后者几乎像Andomar的答案。

CREATE procedure [dbo].[RealTableMergeFromTemp]
with execute as owner
AS
BEGIN
BEGIN TRANSACTION RealTableDataMerge
SET XACT_ABORT ON


DECLARE @columnNameList nvarchar(MAX) =
STUFF((select ',' + a.name
from sys.all_columns a
join sys.tables t on a.object_id = t.object_id
where t.object_id = object_id('[dbo].[RealTable]')
order by a.column_id
for xml path ('')
),1,1,'')


DECLARE @SQLCMD nvarchar(MAX) =N'INSERT INTO [dbo].[RealTable] (' + @columnNameList + N') SELECT * FROM [#Temp]'


SET IDENTITY_INSERT [dbo].[RealTable] ON;
exec(@sqlcmd)
SET IDENTITY_INSERT [dbo].[RealTable] OFF


COMMIT TRANSACTION RealTableDataMerge
END


GO

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

有一个或多个列具有自动递增属性，或者该属性的值将作为约束计算。你要修改这一列。

2)您可以打开IDENTITY_INSERT，然后执行插入查询，最后关闭IDENTITY_INSERT。

建议:按照第一步进行，因为这是更合适和有效的方法。

更多信息请阅读这篇关于SQL-helper的文章。

请确保从中选择记录的表中的列名、数据类型和顺序与目标表完全相同。唯一的区别应该是目标表有一个标识列作为第一列，这在源表中是没有的。

当我执行“INSERT INTO table_Dest SELECT * FROM table_source_linked_server_excel”时，我也遇到了类似的问题。表格有115列。

我有两个这样的表，我从Excel(作为链接服务器)加载数据到数据库中的表。在数据库表中，我添加了一个名为“id”的标识列，这在源Excel中是不存在的。对于一个表，查询运行成功，在另一个表中，我得到了错误“表中标识列的显式值只能在使用列列表时指定，IDENTITY_INSERT是在SQL Server上”。这是令人困惑的，因为这两个查询的场景完全相同。所以我对此进行了调查，我发现在查询中，我用INSERT into得到错误。SELECT *:

1. 源表中的一些列名被修改，但值是正确的
2. SELECT *所选择的实际数据列之外还有一些额外的列。我通过使用源Excel表(链接服务器下)上的“脚本表作为>选择>新查询窗口”选项发现了这一点。在Excel中，在最后一列之后有一个隐藏列，尽管它没有任何数据。我在源Excel表格中删除了该列并保存了它。

在做了以上两个更改之后，INSERT INTO…SELECT *运行成功。目标表中的标识列按照预期为每个插入的行生成标识值。

因此，即使目标表可能有源表中没有的标识列，INSERT INTO..如果源和目标中的名称、数据类型和列顺序完全相同，则SELECT *将成功运行。

希望它能帮助到别人。

我使用以下命令创建一个与表完全相同但没有标识的临时表:

SELECT TOP 0 CONVERT(INT,0)myid,* INTO #temp FROM originaltable


ALTER TABLE #temp DROP COLUMN id


EXEC tempdb.sys.sp_rename N'#temp.myid', N'id', N'COLUMN'

获得关于重命名的警告，但没有大问题。 我在生产类系统中使用这种方法。有助于确保复制将跟随将来的任何表修改，并且所产生的临时文件能够在任务中获得额外的行。请注意，PK约束也被删除了-如果你需要它，你可以在最后添加它