我试着做这个查询
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 Server不允许您在标识列中插入显式值,除非您使用列列表。因此,你有以下选项:
制作一个列列表(手动或使用工具,见下文)
OR
将tbl_A_archive中的标识列设置为常规的非标识列:如果您的表是一个归档表,并且总是为标识列显式指定一个值,那么为什么还需要标识列呢?只需要使用常规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,…包含表中所有列的名称。如果您想自动生成列列表,请查看Dave的答案或Andomar的答案。
方案二详细说明
不幸的是,仅仅将单位int列的“类型”更改为非单位int列是不可能的。基本上,你有以下几种选择:
如果存档表还不包含数据,则删除该列并添加一个不带标识的新列。
OR
使用SQL Server Management Studio将存档表中的标识列的标识规范/(是标识)属性设置为No。在幕后,这将创建一个脚本来重新创建表并复制现有数据,因此,要做到这一点,您还需要取消设置Tools/Options/Designers/ table和Database Designers/Prevent保存需要重新创建表的更改。
OR
使用此回答中描述的解决方法之一:从表中的列中删除标识
如果“归档”表是您的主表的精确副本,那么我只是建议您删除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到->新建查询编辑器窗口。
如果你不是,那么类似的查询应该有助于作为一个起点:
SELECT SUBSTRING(
(SELECT ', ' + QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tbl_A'
ORDER BY ORDINAL_POSITION
FOR XML path('')),
3,
200000);
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'
2)这可能是创建列最简单的方法,
如果你有SQL Server SSMS。
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('')
(删除最后一个逗号(','))只需复制过去的列名。
这应该有用。我刚碰到你的问题
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;
不幸的是,似乎您确实需要包含标识列的列列表来插入指定标识的记录。但是,您不必在SELECT中列出列。
正如@Dave Cluderay建议的那样,这将导致一个格式化的列表供您复制和粘贴(如果小于200000字符)。
我添加了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);
如果您想通过存储过程将值从一个表插入到另一个表。我用了这个和这个,后者和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
有一个或多个列具有自动递增属性,或者该属性的值将作为约束计算。你要修改这一列。
有两种解决方法
1)显式地提到其他列并只设置它们的值,PrimaryKey或自动递增列值将自动设置。
2)您可以打开IDENTITY_INSERT,然后执行插入查询,最后关闭IDENTITY_INSERT。
建议:按照第一步进行,因为这是更合适和有效的方法。
要了解更多信息,请阅读关于SQL-helper的文章。
请确保从中选择记录的表中的列名、数据类型和顺序与目标表完全相同。唯一的区别应该是目标表有一个标识列作为第一列,这在源表中是没有的。
当我执行“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 *将成功运行。
希望它能帮助到别人。
我使用以下命令创建一个与表完全相同但没有标识的临时表:
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约束也被删除了-如果你需要它,你可以在最后添加它。