为数据集插入关闭 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 命令打开).我知道我一次只能对一张桌子这样做，而且我是.

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.

马克

这篇关于为数据集插入关闭 IDENTITY_INSERT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！