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.