У меня есть script, который создает всю базу данных и вставляет все записи в несколько десятков таблиц. Он отлично работает, если во время обработки не возникает какой-либо проблемы, а таблица остается с IDENTITY_INSERT ON, когда script выходит из строя во время вставки и до того, как он снова может быть отключен.
При этом script автоматически выходит из строя при попытке запустить его снова, при этом ошибка "IDENTITY_INSERT уже включена для таблицы xx", когда мы входим в вставку для первой таблицы.
Как отказоустойчивость, я хотел бы убедиться, что для IDENTITY_INSERT установлено значение OFF для всех таблиц, прежде чем запускать оставшуюся часть обработки в настройке script.
В качестве альтернативы мы могли бы закрыть соединение MS SQL и снова открыть его, что, как я понимаю, очистит все значения IDENTITY_INSERT для сеанса подключения.
Какой лучший способ сделать это и предотвратить ошибки "уже включен"?
Динамический 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 в другое окно запроса и запустите
EXEC sp_MSforeachtable @command1="SET IDENTITY_INSERT ? OFF"
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)'
Основываясь на ответе Линна, если вы слишком ленитесь, чтобы выполнить это более чем на один шаг - это должно выполняться на всех таблицах, где есть столбец идентификаторов.
Caveat тестируется только в 2012 году, и sp_MSforeachtable, конечно, полностью не поддерживается...
Основываясь на ответе @KevD - он отлично работал для отключения, но здесь больше для включения.
Чтобы отключить все вставки, в которых они должны быть отключены, используйте -
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 2014 и 2016