How can I reseed an identity column in a T-SQL table variable?

Question

I have a T-SQL table variable (not a table) which has an auto incrementing identity column. I want to clear all data from this variable and reset the identity column value to 1. How can this be done?

Solution 1

If you're using a table variable, you can't do it. If it were a table, you could truncate it or use DBCC CHECKIDENT. But, if you have to use a table variable, you have to use something other than an identity column. Or, more accurately, use the identity column in your table variable but output using ROWNUMBER:

DECLARE @t table (pkint int IDENTITY(1,1), somevalue nvarchar(50))
INSERT INTO @t (somevalue) VALUES( 'one')
INSERT INTO @t (somevalue) VALUES('twp')
INSERT INTO @t (somevalue) VALUES('three')
SELECT row_number() OVER (ORDER BY pkint), somevalue FROM @t
DELETE FROM @t
INSERT INTO @t (somevalue) VALUES('four')
SELECT row_number() OVER (ORDER BY pkint), somevalue FROM @t

It's the best you can do with the table variable.

Solution 2

Truncating the table will dump ALL the data, and reset the identity seed.

Otherwise, you can use this call to reset the identity while retaining any of the data:

DBCC CHECKIDENT (yourtableName, reseed, @NewStartSeedValue)

Solution 3

I suggest you use two table variables. The @Table1 has an identity seed on the first column. @Table2 has the same first column but no identity seed on it.

As you loop through your process,

Insert into @Table2 from @Table1

then Delete From both Tables as your Process Loops.

On your first pass, the @Table2 will have a a sequential number in the first row starting at 1.

The second time through the loop your second table might have sequential numbers in the first column starting at say 1081. But if you select the minimum value to a variable

(Select @FixSeed = min(RowID) From @Table2)

Then you can update @Table2 to make RowID start at 1 as follows:

Update @Table2  Set  RowID = RowID - @FixSeed +1

Hope this helps