Как автоматически перезаряжать после использования identity_insert?

Question

Я недавно мигрировал из базы данных PostgreSQL в базу данных SQL Server. Чтобы переключить данные, мне нужно было включить IDENTITY_INSERT. Приходите, чтобы узнать, что я получаю всевозможные странные ошибки из-за дублирования значений идентификаторов (которые устанавливаются в качестве первичных ключей) при выполнении вставки в любую из таблиц.

У меня довольно много столов. Какой будет самый простой способ автоматического повторного заполнения идентификатора каждой таблицы, чтобы она max(RID)?

Answer 1

Используйте информацию в этой ссылке в сочетании с функцией SQL, которая получает максимальное значение (RID) для каждой таблицы, которую необходимо сбросить. Например, если вы хотите запустить начальный начальный ключ с 25000, используйте код ниже (StartSeedValue - 1)

DBCC CHECKIDENT('myTable', RESEED, 24999)

Таким образом, в сочетании, вы должны получить что-то вроде этого

DECLARE @maxVal INT
SELECT @maxVal = ISNULL(max(ID),0)+1 from mytable
DBCC CHECKIDENT('mytable', RESEED, @maxVal)

Извините за псевдокод, некоторое время назад я написал функцию SQL:)

РЕДАКТИРОВАТЬ:

Спасибо за улов, поменял INTEGER на INT

USE YourDBName
GO 
SELECT *
FROM sys.Tables
GO 

Это даст вам список всех пользовательских таблиц в базе данных. Используйте этот запрос в качестве "цикла", и это должно позволить сбросить начальные значения во всех таблицах.

Answer 2

Томми ответит правильно, но если я правильно читаю документацию, это можно упростить до:

DBCC CHECKIDENT ('myTable')

Согласно документации:

Если текущее значение идентификатора для таблицы меньше максимального значения идентификатора, хранящегося в столбце идентификаторов, оно сбрасывается с использованием максимального значения в столбце идентификаторов.

Это избавляет вас от необходимости искать максимальный идентификатор вручную и поддерживается начиная с SQL Server 2005.

Это должно работать в оригинальном случае OP. Однако в документации упоминаются два случая, когда это не сработает, и вам придется обратиться к решению Томми с поиском максимального значения идентификатора вручную:

• Текущее значение идентификатора больше максимального значения в таблице.
• Все строки удаляются из таблицы.

Answer 3

Возможно, самый простой способ (как бы безумно это ни звучало и как вонючий код) - просто запустить DBCC CHECKIDENT в два раза так:

-- sets all the seeds to 1
exec sp_MSforeachtable @command1 = 'DBCC CHECKIDENT (''?'', RESEED, 1)'

-- run it again to get MSSQL to figure out the MAX/NEXT seed automatically
exec sp_MSforeachtable @command1 = 'DBCC CHECKIDENT (''?'')'

Готово.

Если вы хотите, вы можете запустить его еще раз, чтобы увидеть, на что были установлены все семена:

-- run it again to display what the seeds are now set to
exec sp_MSforeachtable @command1 = 'DBCC CHECKIDENT (''?'')'

Это просто творческий способ воспользоваться комментариями из документации:

Если текущее значение идентификатора для таблицы меньше максимального значения идентификатора, хранящегося в столбце идентификаторов, оно сбрасывается с использованием максимального значения в столбце идентификаторов.

Answer 4

Откат дел -

Я тестирую в своей базе данных и работаю, только если я использую этот код, указанный здесь ранее (с модификацией +1 - нам это не нужно).

DECLARE @maxVal INT
SELECT @maxVal = ISNULL(max(codsequencia),0) from teste_sequencial
DBCC CHECKIDENT(teste_sequencial, RESEED, @maxVal)

Обратите внимание: если вы поставите +1 после части 'ISNULL', следующий столбец идентификаторов будет прыгать +1, например - текущий столбец 10, после кода следующий будет 11, если вы используете +1 после isnull, будет +12.

И, коды:

DBCC CHECKIDENT (teste_sequencial)

Exec sp_MSforeachtable 'DBCC CHECKIDENT(''?'')'

У меня не работал вообще в случаях отката дела. Если вы откроете транзакцию и выполните откат, повторное заполнение начнется с последнего номера, использованного в транзакции.

Answer 5

Добавление к ответу @010110110101 - чтобы пропустить таблицы, у которых нет столбца идентификаторов:

      -- sets all the seeds to 1
exec sp_MSforeachtable @command1 = 'IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1 DBCC CHECKIDENT (''?'', RESEED, 1)'

-- run it again to get MSSQL to figure out the MAX/NEXT seed automatically
exec sp_MSforeachtable @command1 = 'IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1 DBCC CHECKIDENT (''?'')'