为数据集插入关闭 IDENTITY_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 命令打开).我知道我一次只能对一张桌子这样做，而且我是.
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 设置为 OFF 时，无法在表订单"中插入标识列的显式值.
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.
有什么想法吗?
更新
亚历克斯&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 - Third to n - 我的各种 sql 命令，包括 select &插入的
虽然命令之间总是有一个 exec sp_reset_connection，但我相信这是导致 Identity_Insert 设置值丢失的原因.
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?
推荐答案
你的选项搞混了:
SET IDENTITY_INSERT orders ON
将开启将特定值(您指定)插入到具有 IDENTITY 列的表中的功能.
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.
马克
相关文章