如何检查 SQL Server 中的 IDENTITY_INSERT 设置为 ON 还是 OFF? [英] How do you check if IDENTITY_INSERT is set to ON or OFF in SQL Server?

问题描述

我已经搜索过这个问题，但出现问题的帖子往往会从不理解问题的人那里得到答案.

I've searched for this, but threads in which it appeared tended to have answers from people who didn't understand the question.

采用以下语法:

SET IDENTITY_INSERT Table1 ON

你如何做更像这样的事情:

How do you do something more like this:

GET IDENTITY_INSERT Table1

我不想对数据库中的数据或设置做任何事情来获取这些信息.谢谢！

I don't want to do anything whatsoever to the data in the database or to the settings to get this information though. Thanks!

推荐答案

由于 SET IDENTITY_INSERT 是会话敏感的，所以它在缓冲区级别进行管理，而不存储在某处.这意味着我们不需要检查 IDENTITY_INSERT 状态，因为我们在当前会话中从不使用这个关键字.

Since SET IDENTITY_INSERT is a session sensitive, it is managed in buffer level without storing somewhere. This means we do not need to check the IDENTITY_INSERT status as we never use this key word in current session.

抱歉，没有帮助.

好问题:)

来源:此处

更新可能有一些方法可以做到这一点，也可以在我链接的网站上看到，IMO，太费力了没有用.

Update There are ways maybe to do this, also seen in the site I linked, IMO, it is too much effort to be useful.

if

(select max(id) from MyTable) < (select max(id) from inserted)

--Then you may be inserting a record normally

BEGIN
    set @I = 1 --SQL wants something to happen in the "IF" side of an IF/ELSE
END

ELSE --You definitely have IDENTITY_INSERT on.  Done as ELSE instead of the other way around so that if there is no inserted table, it will run anyway


BEGIN
.... Code that shouldn't run with IDENTITY_INSERT on
END

这篇关于如何检查 SQL Server 中的 IDENTITY_INSERT 设置为 ON 还是 OFF?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！