如何在SQL Server 2008中一次为多个表设置IDENTITY_INSERT ON [英] How to SET IDENTITY_INSERT ON in SQL Server 2008 for multiple tables at once

问题描述

我有两个表 tblData1 和 tblData2 ，现在我想从另一个插入了标识插入的表中迁移记录我正在尝试运行如下所示的命令

I have two tables tblData1 and tblData2 and now I want to migrate records from another table with identity insert and I am trying to run a command as shown below

SET IDENTITY_INSERT LP1.dbo.tblData1 ON
GO

SET IDENTITY_INSERT LP1.dbo.tblData2 ON
GO

INSERT INTO LP1.DBO.tblData1 (ID,DATA)
SELECT ID,DATA FROM LP.DBO.tblData1
GO

INSERT INTO LP1.DBO.tblData2 (ID,DATA)
SELECT ID,DATA FROM LP.DBO.tblData2
GO

SET IDENTITY_INSERT LP1.dbo.tblData1 OFF
GO

SET IDENTITY_INSERT LP1.dbo.tblData2 OFF
GO

但是它显示如下错误

IDENTITY_INSERT已经打开表'Sample_Training.dbo.tblData1'。无法对表'dbo.tblData2'执行SET操作

IDENTITY_INSERT is already ON for table 'Sample_Training.dbo.tblData1'. Cannot perform SET operation for table 'dbo.tblData2'

是否可以执行多个 IDENTITY_INSERT 在SQL Server 2008中的时间

Is it possible to perform multiple IDENTITY_INSERT at time in SQL Server 2008

推荐答案

在任何时候，会话中只能有一个表

因此，在启用另一个属性之前，应先将现有属性翻转。

如果有是较少的表，您可以在操作前后打开和关闭表。

如果表数量巨大，则应在操作之前以某种方式自动启用和禁用。

So before enabling the other one, you should turn of existing if any.
If it is lesser number of tables you can turn on and turn off before and after your operations.
If the table count is huge, you should automate somehow to enable and disable before your operations.

这篇关于如何在SQL Server 2008中一次为多个表设置IDENTITY_INSERT ON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！