How to automatically reseed after using identity_insert?

Question

I recently migrated from a PostgreSQL database to a SQL Server database. To switch the data over I had to enable IDENTITY_INSERT. Well come to find out that I get all sorts of strange errors due to duplicate identity values(which are set as primary keys) upon doing an insert in any of the tables.

I have quite a few tables. What would be the easiest way of automatically reseeding the identity of every table so that it is after max(RID)?

Homicidal

DBCC CHECKIDENT('tablename', RESEED) https://mcmap.net/q/473028/-dbcc-checkident-reseed-is-new-value-required – Bailable

Answer 1

Use the information in this link in combination with a SQL function that gets the max(RID) from each table that you need to reset. For instance, if you want to start your primary key seed at 25000, use the code below (StartSeedValue - 1)

DBCC CHECKIDENT('myTable', RESEED, 24999)

So in combination, you should end up with somethink like this

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

Sorry for the Pseudo-code, been awhile since I have written a SQL function :)

EDIT:

Thanks for the catch, changed the INTEGER to INT

USE YourDBName
GO 
SELECT *
FROM sys.Tables
GO 

This will give you a listing of all user tables in the database. Use this query as your 'loop' and that should allow to reset the seeds on all tables.

Cairn

Is there any way to do this for every table automatically though? – Homicidal

+1, but integer is not a sql server data type, so use: DECLARE @maxVal INT and to populate a variable use: SELECT @maxVal = ISNULL(max(ID),0)+1 from mytable – Basilbasilar

@KM good note on the ISNULL check but integer is recognized by SQL Server 2008 – Homicidal

integer is not supported in pre SQl Server 2008, it is INT, and question did not specify 2008. – Basilbasilar

SELECT @maxVal = ISNULL(max(ID),0) from mytable if you want to start ID from 1 – Epiboly

Answer 2

Tommy's answer is correct, but if I am reading documentation right this can be simplified to just:

DBCC CHECKIDENT ('myTable')

According to documentation:

If the current identity value for a table is less than the maximum identity value stored in the identity column, it is reset using the maximum value in the identity column.

This saves you from the need ot look up maximum ID manually, and is supported from SQL Server 2005 onwards.

This should work in original OP case. Documentation mentions however two cases where this will not work, and you have to fall-back to Tommy's solution with looking maximum ID value manually:

• The current identity value is larger than the maximum value in the table.
• All rows are deleted from the table.

Rioux

and even more automatically to all tables : Exec sp_MSforeachtable 'DBCC CHECKIDENT(''?'')' – Silures

Answer 3

Perhaps the easiest way (as crazy as this sounds and as code-smelly as it looks) is to just run DBCC CHECKIDENT twice like this:

-- 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 (''?'')'

Done.

If you want, you can run it once more to see what all the seeds were set to:

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

This is just a creative way to take advantage of the comment from the documentation:

If the current identity value for a table is less than the maximum identity value stored in the identity column, it is reset using the maximum value in the identity column.

Tankage

Answer 4

Rollback cases -

i've testing in my database and only works if i use this code listed here before (with a modification on the +1 - we don't need this).

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

Note, if u put the +1 after the 'ISNULL' part, the next identity column will jump +1, for example - current column 10 , after the code the next will be 11, if u use +1 after isnull , will be +12.

AND, the codes :

DBCC CHECKIDENT (teste_sequencial)

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

Didn't work for me at all in cases rollbacks cases. If u open a transaction , and do the rollback , the reseed will start at the last number used in the transaction.

Psycho

Answer 5

Adding to @010110110101's answer - to skip tables that don't have an identity column:

-- 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 (''?'')'

Honkytonk