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

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

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

Ответы

Ответ 1

Используйте информацию в этой ссылке в сочетании с функцией SQL, которая получает максимальную (RID) из каждой таблицы, которая вам нужна reset. Например, если вы хотите запустить первичное ключевое семя на 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:)

EDIT:

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

USE YourDBName
GO 
SELECT *
FROM sys.Tables
GO

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

Ответ 2

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

DBCC CHECKIDENT ('myTable')

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

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

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

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

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

Ответ 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 (''?'')'

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

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

Ответ 4

Отказоустойчивые случаи -

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

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

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

И, коды:

DBCC CHECKIDENT (teste_sequencial)

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

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