我试着做这个查询
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是一个行和宽都很大的表，也就是说它有很多列。我不想手动输入所有的列。我怎样才能让它工作呢?
当前回答
如果“归档”表是您的主表的精确副本，那么我只是建议您删除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);
如果“归档”表是您的主表的精确副本，那么我只是建议您删除id是标识列的事实。这样你就可以插入它们了。
或者，您可以使用以下语句允许和禁止对表进行标识插入
SET IDENTITY_INSERT tbl_A_archive ON
--Your inserts here
SET IDENTITY_INSERT tbl_A_archive OFF
最后，如果您需要标识列按原样工作，那么您总是可以运行存储的过程。
sp_columns tbl_A_archive
这将返回表中的所有列，然后您可以将其剪切并粘贴到您的查询中。(这几乎总是比使用*更好)
此代码片段显示当标识主键列为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
两者都可以工作，但如果使用#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,....)
这应该有用。我刚碰到你的问题
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);
