我试着做这个查询
INSERT INTO dbo.tbl_A_archive
SELECT *
FROM SERVER0031.DB.dbo.tbl_A
但即使在我跑了之后
set identity_insert dbo.tbl_A_archive on
我得到这个错误消息
表'dbo中标识列的显式值。tbl_A_archive'只能在使用列列表且IDENTITY_INSERT为ON时指定。
tbl_A是一个行和宽都很大的表，也就是说它有很多列。我不想手动输入所有的列。我怎样才能让它工作呢?
当前回答
对于SQL语句，还必须指定列列表。如。
INSERT INTO tbl (idcol1,col2) VALUES ( value1,value2)
而不是
INSERT INTO tbl VALUES ( value1,value2)
其他回答
此代码片段显示当标识主键列为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
我认为这个错误是由于表定义中的列数和插入查询中的列数不匹配造成的。此外，输入的值省略了列的长度。 因此，只需检查表定义来解决这个问题
对于SQL语句，还必须指定列列表。如。
INSERT INTO tbl (idcol1,col2) VALUES ( value1,value2)
而不是
INSERT INTO tbl VALUES ( value1,value2)
请确保从中选择记录的表中的列名、数据类型和顺序与目标表完全相同。唯一的区别应该是目标表有一个标识列作为第一列，这在源表中是没有的。
当我执行“INSERT INTO table_Dest SELECT * FROM table_source_linked_server_excel”时，我也遇到了类似的问题。表格有115列。
I had two such tables where I was loading data from Excel (As linked server) into tables in database. In database tables, I had added an identity column called 'id' that was not there in source Excel. For one table the query was running successfully and in another I got the error "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". This was puzzling as the scenario was exactly same for both the queries. So I investigated into this and what I found was that in the query where I was getting error with INSERT INTO .. SELECT *:
源表中的一些列名被修改，但值是正确的 SELECT *所选择的实际数据列之外还有一些额外的列。我通过使用源Excel表(链接服务器下)上的“脚本表作为>选择>新查询窗口”选项发现了这一点。在Excel中，在最后一列之后有一个隐藏列，尽管它没有任何数据。我在源Excel表格中删除了该列并保存了它。
在做了以上两个更改之后，INSERT INTO…SELECT *运行成功。目标表中的标识列按照预期为每个插入的行生成标识值。
因此，即使目标表可能有源表中没有的标识列，INSERT INTO..如果源和目标中的名称、数据类型和列顺序完全相同，则SELECT *将成功运行。
希望它能帮助到别人。
如果您想通过存储过程将值从一个表插入到另一个表。我用了这个和这个，后者和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
