数据库范围内相当于 SET IDENTITY_INSERT OFF

发布于 2024-07-07 23:54:53 字数 532 浏览 8 评论 0原文

在我的永恒 saga 从 SQL 脚本插入 140 万行数据，我'我们编写了一个基本的 WinForms 应用程序，它获取脚本的每一行并单独执行它。

但是，由于原始脚本包含

SET IDENTITY_INSERT [Table] OFF

并且 SET 是会话范围的命令，因此此设置在每次 SQL 调用时都会丢失，这意味着每一行都会失败。有没有办法在数据库范围内为整个表设置 IDENTITY_INSERT 关闭，这样我就可以进行这些单独的调用而不会失败？或者也许我可以通过在每一行附加一个命令来告诉它忽略身份规范？

原文

In my eternal saga to insert 1.4 million rows of data from a SQL script, I've written a basic WinForms app that takes each line of the script and executes it individually.

However, because the original script contained

SET IDENTITY_INSERT [Table] OFF

and SET is a session-wide command, this setting is getting lost on every SQL call, meaning that each line is failing. Is there a way to set IDENTITY_INSERT off for the whole table, database-wide just so I can make these individual calls without them failing? Or perhaps I can tell it to ignore the identity specification by appending a command to each line?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

笑忘罢 2024-07-14 23:54:54

我相信 IDENTITY INSERT 每个会话一次只能覆盖一个表。

您可能必须一次批处理 2 或 300 个插入语句，并在每个批处理之前添加 ident 插入。

所以应用程序将执行这样的块......

SET IDENTITY_INSERT [Table] OFF;
INSERT INTO TABLE VALUES (1, 'a');
INSERT INTO TABLE VALUES (2, 'b');
INSERT INTO TABLE VALUES (3, 'c');
INSERT INTO TABLE VALUES (4, 'd');
INSERT INTO TABLE VALUES (5, 'e');
SET IDENTITY_INSERT [Table] ON;

I believe that IDENTITY INSERT can only be overridden one table at a time, per session.

You will have to maybe batch up 2 or 300 insert statements at a time and precede each batch with the ident insert.

so the app will execute a block like this....

SET IDENTITY_INSERT [Table] OFF;
INSERT INTO TABLE VALUES (1, 'a');
INSERT INTO TABLE VALUES (2, 'b');
INSERT INTO TABLE VALUES (3, 'c');
INSERT INTO TABLE VALUES (4, 'd');
INSERT INTO TABLE VALUES (5, 'e');
SET IDENTITY_INSERT [Table] ON;

回复收藏 0 原文