How to automatically reseed after using identity_insert?

sql-server identity identity-insert
Earlz picture Earlz · May 27, 2010 · Viewed 22.1k times · Source

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

Answer

Tommy picture Tommy · May 27, 2010

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.

Related questions

Auto increment primary key in SQL Server Management Studio 2012

How do I auto increment the primary key in a SQL Server database table, I've had a look through the forum but can't see how. I've looked the the properties but can't see an option, I have seen an answer …

Read More
How to change identity column values programmatically?

I have a MS SQL 2005 database with a table Test with column ID. ID is an identity column. I have rows in this table and all of them have their corresponding ID auto incremented value. Now I would like to …

Read More
Handling identity columns in an "Insert Into TABLE Values()" statement?

In SQL Server 2000 or above is there anyway to handle an auto generated primary key (identity) column when using a statement like the following? Insert Into TableName Values(?, ?, ?) My goal is to NOT use the column names at all.

Read More