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.
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, it seems you can't do this with table variables.....
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