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)
?
IDENTITY_INSERT off in SQL Server Once you have turned the IDENTITY_INSERT option OFF, you cannot insert explicit values in the identity column of the table. Also, the value will be set automatically by increment in the identity column if you try to insert a new record.
The set identity_insert command in SQL Server, as the name implies, allows the user to insert explicit values into the identity column of a table.
Answers. In a given session , you can have only one table's IDENTITY_INSERT property set to ON. You can use set IDENTITY_INSERT state (on/off) only at excute or run time.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With