关闭所有表的IDENTITY_INSERT

Question

我有一个脚本可以创建整个数据库并将所有记录插入到几十个表中。它一切正常，除非在处理过程中出现问题，并且在脚本在插入之前失败时，某个表保留了IDENTITY_INSERT ON的状态。

当发生这种情况时，如果再次尝试运行脚本，就会自动失败并显示错误“IDENTITY_INSERT已为表xx打开”，因为我们进入第一个表的插入。

为了确保在运行设置脚本的其余部分之前，对于所有表都将IDENTITY_INSERT设置为OFF，我想作为一种保险措施。

另外，我们可以关闭MS SQL连接并重新打开它，这样我理解会清除连接会话中的所有IDENTITY_INSERT值。

什么是最好的方法来做到这一点，并防止“已经打开”的错误？

- m_evangelista

---

此应用程序需要与2005年及以上版本兼容，因此最好提供适用于所有近期和即将发布的版本的解决方案。 - m_evangelista

我的解决方案应该适用于05及以后的版本。 - John Dewey

Answer 1

动态 SQL：

select 'set identity_insert ['+s.name+'].['+o.name+'] off'
from sys.objects o
inner join sys.schemas s on s.schema_id=o.schema_id
where o.[type]='U'
and exists(select 1 from sys.columns where object_id=o.object_id and is_identity=1)

然后将生成的 SQL 复制并粘贴到另一个查询窗口中并运行。

- John Dewey

---

我想在我的代码中使用这个来进行批量数据库插入。你能建议一些如何在JPA中实现这个的方法吗？ - muasif80

这个查询很棒，因为它检查哪些表启用了标识，然后为其生成“关闭”脚本。当您想要重新启用时，只需将查询中的“off”更改为“on”。 - Newteq Developer

Answer 2

EXEC sp_MSforeachtable @command1="SET IDENTITY_INSERT ? OFF"

- Lynn Crumbling

---

我很喜欢这个...除了，这个数据库中并非每张表都有一个标识字段，所以操作失败了。否则我觉得它会是完美的。 - m_evangelista

根据http://technet.microsoft.com/en-us/library/aa259221(v=sql.80).aspx#feedback，每个会话中只能有一个表的IDENTITY_INSERT属性设置为ON。 - Tomas Kubes

@qub1n OP没有要求将属性设置为ON。 - Lynn Crumbling

我知道这不是任务，但我希望对那些像我一样正在寻找SET IDENTITY_INSERT ON的人有用的信息。 - Tomas Kubes

Answer 3

EXEC sp_MSforeachtable @command1="PRINT '?'; SET IDENTITY_INSERT ? OFF",
@whereand = ' AND EXISTS (SELECT 1 FROM sys.columns WHERE object_id = o.id  AND is_identity = 1)'

在Lynn的回答基础上，如果您懒得分步骤执行此操作 - 这应该在所有具有标识列的表上运行。

注意，仅在2012年进行了测试， sp_MSforeachtable 当然是完全不受支持的...

- KevD

Answer 4

在@KevD的答案基础上进行改进-禁用功能很好，但是这里还有更多关于启用功能的内容。

要禁用所有需要禁用的身份插入，使用-

To enable identity inserts where they need to be enabled, use -

要启用需要启用的身份插入，请使用 -

EXEC sp_MSforeachtable @command1="PRINT '?'; SET IDENTITY_INSERT ? OFF",
@whereand = ' AND EXISTS (SELECT 1 FROM sys.columns WHERE object_id = o.id  
AND is_identity = 1) and o.type = ''U'''

为了启用所有必须启用的身份插入功能，请使用 -

EXEC sp_MSforeachtable @command1="PRINT '?'; SET IDENTITY_INSERT ? ON",
@whereand = ' AND EXISTS (SELECT 1 FROM sys.columns WHERE object_id = o.id  
AND is_identity = 1) and o.type = ''U'''

已在 Sql server 2014 和 2016 上进行测试

- Kumar Vaibhav

---

如果已经打开，则打开失败并退出EXEC。有没有一种方法可以先测试位？ - Maury Markowitz

Answer 5

我曾经遇到过类似的问题，但我宁愿在生产环境中不使用未经记录的存储过程。为了自动化解决此问题，我在@John Dewey的答案上进行了改进，并将其放入了游标中。这样可以在407毫秒内迭代处理699个表。

DECLARE @sql NVARCHAR(500) -- SQL command to execute

DECLARE sql_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT 'SET identity_insert ['+s.name+'].['+o.name+'] OFF'
FROM sys.objects o
INNER JOIN sys.schemas s on s.schema_id=o.schema_id
WHERE o.[type]='U'
AND EXISTS(SELECT 1 FROM sys.columns WHERE object_id=o.object_id AND is_identity=1)

OPEN sql_cursor
FETCH NEXT FROM sql_cursor INTO @sql

WHILE @@FETCH_STATUS = 0
    BEGIN
        EXECUTE sp_executesql @sql  --> Comment this out to test
        -- PRINT @sql   --> Uncomment to test or if logging is desired
        FETCH NEXT FROM sql_cursor INTO @sql
    END

CLOSE sql_cursor
DEALLOCATE sql_cursor

如果您不喜欢游标，它也可以很容易地转换为 while 循环。

- DBADon