Saturday, September 15, 2012

Reseed Identity Using Variable

In a sproc that involves creating a copy of a production SQL table for testing, I wanted to reseed the identity of the new table to 1 more than the max of the production key value. 

I figured at first I'd just use the appropriate syntax for DBCC and just use a variable already at hand containing the max production value and add 1 to it, like so:

 DECLARE @MaxID int

 -- Grab the max identity value from production.
 SELECT @MaxID = MAX(ID) FROM dbo.MyProductionTable

 DBCC CHECKIDENT (MyProductionTable_COPY, reseed, @MaxID + 1) 


SQL, however, would have nothing of it:
Incorrect syntax near '+'.

To work around this, I decided to try creating a dynamic SQL string to incorporate the variable arithmetic:

 DECLARE @MaxID int
 DECLARE @SQL nvarchar(100)

 -- Grab the max identity value from production.
 SELECT @MaxID = MAX(ID) FROM dbo.MyProductionTable

 SET @SQL = 'DBCC CHECKIDENT (MyProductionTable_COPY, reseed, ' 
 + CAST(@MaxID + 1 AS nvarchar(10)) 
 + ')'


Now I can create the table and set its starting identity value to 1 greater than in production using a variable despite DBCC's syntax dictating otherwise.