关闭IDENTITY_INSERT以插入数据集 [英] Turn off IDENTITY_INSERT for Dataset insert
问题描述
我正在使用数据集来插入从旧数据库转换而来的数据。要求是保持当前的Order_ID号。
I am using a dataset to insert data being converted from an older database. The requirement is to maintain the current Order_ID numbers.
我尝试使用：
SET IDENTITY_INSERT orders ON;
当我在SqlServer Management Studio中时，此方法可以成功
This works when I'm in SqlServer Management Studio, I am able to successfully
INSERT INTO orders (order_Id, ...) VALUES ( 1, ...);
但是，它不允许我通过我正在使用的数据集插入来执行此操作转换脚本。基本上看起来像这样：
However, it does not allow me to do it via the dataset insert that I'm using in my conversion script. Which looks basically like this:
dsOrders.Insert(oldorderId, ...);
在此过程中，我也运行了SQL（SET IDENTITY_INSERT订单为ON）。我知道我一次只能对一张桌子这样做，而我是。
I've run the SQL (SET IDENTITY_INSERT orders ON) during the process too. I know that I can only do this against one table at a time and I am.
我一直在收到此异常：
尝试将值插入订单表
中的异常System.Data.SqlClient.SqlException：设置IDENTITY_INSERT时，无法在表 orders中为标识列插入显式值到[关闭]。
Exception when attempting to insert a value into the orders table System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'orders' when IDENTITY_INSERT is set to OFF.
有什么想法吗？
更新
AlexS& AlexKuznetsov曾提到Set Identity_Insert是一个连接级别设置，但是，当我在SqlProfiler中查看SQL时，我注意到了几个命令。
AlexS & AlexKuznetsov have mentioned that Set Identity_Insert is a connection level setting, however, when I look at the SQL in SqlProfiler, I notice several commands.
- 第一个-
SET IDENTITY_INSERT DEAL ON - 第二个-
exec sp_reset_connection - 第三到n-我的各种sql命令，包括select&插入的
尽管在命令之间总是有 exec sp_reset_connection
There is always an exec sp_reset_connection between the commands though, I believe that this is responsible for the loss of value on the Identity_Insert setting.
是否可以阻止我的数据集进行连接重置？
Is there a way to stop my dataset from doing the connection reset?
推荐答案
您混合使用了以下选项：
You have the options mixed up:
SET IDENTITY_INSERT orders ON
将打开 ON 插入特定值的功能（即
will turn ON the ability to insert specific values (that you specify) into a table with an IDENTITY column.
SET IDENTITY_INSERT orders OFF
再次关闭该行为，将恢复正常行为（由于IDENTITY列是自动生成的，因此无法为其指定值）。
Turns that behavior OFF again and the normal behavior (you can't specify values for IDENTITY columns since they are auto-generated) is reinstated.
马克
这篇关于关闭IDENTITY_INSERT以插入数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
