含IDENTITY与timestamp列的单表备份恢复报错解决方案咨询

直接使用以下SQL语句即可完成数据导入：

SET IDENTITY_INSERT [Production].[dbo].[SystemInfos] ON
INSERT INTO [Production].[dbo].[SystemInfos]
(ID, Ident, Value, Data) -- 排除timestamp类型的Stamp列
SELECT ID, Ident, Value, Data FROM [Archive].[dbo].[SystemInfos]
SET IDENTITY_INSERT [Production].[dbo].[SystemInfos] OFF

如果需要让数据库自动处理timestamp列的取值，也可以用DEFAULT关键字显式指定：

SET IDENTITY_INSERT [Production].[dbo].[SystemInfos] ON
INSERT INTO [Production].[dbo].[SystemInfos]
(ID, Ident, Value, Data, Stamp)
SELECT ID, Ident, Value, Data, DEFAULT FROM [Archive].[dbo].[SystemInfos]
SET IDENTITY_INSERT [Production].[dbo].[SystemInfos] OFF

错误原因解析 #

• 第一次报错：timestamp（本质是rowversion类型）是数据库自动维护的行版本标识，不允许插入显式值，必须要么排除该列让系统生成新值，要么用DEFAULT委托系统处理。
• 第二次报错：开启IDENTITY_INSERT后，必须显式指定插入列的列表，不能用SELECT *，否则数据库无法确认你是否要对IDENTITY列插入自定义值。

核心注意事项 #

• 开启IDENTITY_INSERT后，INSERT语句必须明确列出包含IDENTITY列（ID）在内的目标列。
• timestamp/rowversion列禁止手动赋值，只能通过排除列或指定DEFAULT的方式让数据库自动生成。

内容的提问来源于stack exchange，提问作者Lucyper