如何在 SQL Server 2008 中一次为多个表设置 IDENTITY_INSERT ON
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27615391/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to SET IDENTITY_INSERT ON in SQL Server 2008 for multiple tables at once
提问by Dinesh Reddy Alla
I have two tables tblData1and tblData2and now I want to migrate records from another table with identity insert and I am trying to run a command as shown below
我有两个表tblData1，tblData2现在我想从另一个带有身份插入的表中迁移记录，我正在尝试运行如下所示的命令
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
But it is showing error as below
但它显示如下错误
IDENTITY_INSERT is already ON for table 'Sample_Training.dbo.tblData1'. Cannot perform SET operation for table 'dbo.tblData2'
表“Sample_Training.dbo.tblData1”的 IDENTITY_INSERT 已经打开。无法对表 'dbo.tblData2' 执行 SET 操作
Is it possible to perform multiple IDENTITY_INSERTat time in SQL Server 2008
是否可以IDENTITY_INSERT在 SQL Server 2008 中一次执行多个
回答by knkarthick24
At any time, only one table in a session can have the IDENTITY_INSERT property set to ON.
在任何时候，会话中只有一个表可以将 IDENTITY_INSERT 属性设置为 ON。
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.
因此，在启用另一个之前，您应该关闭现有（如果有）。
如果表的数量较少，您可以在操作前后打开和关闭。
如果表数量很大，您应该在操作之前以某种方式自动启用和禁用。
回答by P?????
Did you try changing the order
您是否尝试更改顺序
go
SET IDENTITY_INSERT LP1.dbo.tblData1 ON
INSERT INTO LP1.DBO.tblData1
(ID,DATA)
SELECT ID,DATA
FROM LP.DBO.tblData1
SET IDENTITY_INSERT LP1.dbo.tblData1 OFF
GO
SET IDENTITY_INSERT LP1.dbo.tblData2 ON
INSERT INTO LP1.DBO.tblData2
(ID,DATA)
SELECT ID,DATA
FROM LP.DBO.tblData2
SET IDENTITY_INSERT LP1.dbo.tblData2 OFF
GO
回答by Amir Pelled
You can only set Identity_Insert for one table at a time in a single session. If there are no data dependancies between the tables, then you can open several sessions, each handling a different set of tables. Each session can set one table for identy_insert.
在单个会话中一次只能为一个表设置 Identity_Insert。如果表之间没有数据依赖关系，那么您可以打开多个会话，每个会话处理一组不同的表。每个会话可以为identy_insert 设置一张表。
