Programmatically set identity seed in a table variable

Question

I need to create a table variable with an identity seed that starts with the max value of a field in another table?

I've tried this:

DECLARE @IdentitySeed int

SET @IdentitySeed = (SELECT MAX(HHRecId) +1 FROM xxx )

DECLARE @HH TABLE (
    HHId INT IDENTITY(@IdentitySeed,1)
    ,AddressType CHAR(1)
    ,Address1 VARCHAR(100)
    ,City VARCHAR(50)
    ,State VARCHAR(2)
    ,Zip VARCHAR(5)
    ,LastName VARCHAR(50)
    )

But that gives a syntax error.

For now, I've added another int column to the table variable and update that with the sum of the identity column and @IdentitySeed but I would like to find a way to do that without the update.

Answer 1

I believe you can do this, but it'll have to be done in dynamic SQL - declare the tableVar in the dynamic SQL and use it there too!

It would surely be easier and result in faster code if you started it at 1 and had a secondary ID field that is calculated as MAX(HHRecId) + ID.

Answer 2

You can check the current value of an IDENTITY column by using:

DBCC CHECKIDENT (#HH)

and you can also change that later on using:

DBCC CHECKIDENT (#HH, RESEED, 42)

and that also works with a variable for the new value:

DBCC CHECKIDENT (#HH, RESEED, @IdentitySeed)

It works for local and global temporary tables (i.e. CREATE TABLE #HH (...) or CREATE TABLE ##HH (....) - but it doesn't seem to work with table variables :-(

Sorry, I it seems you can't do this with table variables.....

Marc