Is there anyway to reset the identity of a Table Variable?

user_16241 0
Viewed 34300

Say I have a table variable:

DECLARE @MyTableVar TABLE (ID INT IDENTITY(1,1), SomeData NVARCHAR(300))

After I have inserted 250 rows, I need to "Start Over" with the table. I do this:

DELETE FROM @MyTableVar

Is there anything I can do to the table variable so that this:

insert into @MyTableVar Values("TestData")
select * from @MyTableVar

will return this:

_______________________________
|    ID     |    SomeData     |
|___________|_________________|
|           |                 |   
|     1     |    TestData     |        
|___________|_________________|

instead of this:

_______________________________
|    ID     |    SomeData     |
|___________|_________________|
|           |                 |   
|    251    |    TestData     |        
|___________|_________________|
sql-server-2008 tsql sql-server
12 Answers

I just had this idea and it works!!! :

declare @TableVariable table (
    IdentityColumn int identity(1,1),
    SomeOtherValue int,
    DesiredResult int
)
declare @FirstIdentityValueEachTimeYouLoadDataToTable int
declare @Count int
set @Count = 1

while @Count <= 5
begin
    delete @TableVariable

    insert into @TableVariable (SomeOtherValue) select 45
    insert into @TableVariable (SomeOtherValue) select 90
    insert into @TableVariable (SomeOtherValue) select 2

    select @FirstIdentityValueEachTimeYouLoadDataToTable = min(IdentityColumn) from @TableVariable

    Update @TableVariable set DesiredResult = IdentityColumn - @FirstIdentityValueEachTimeYouLoadDataToTable + 1

    select * from @TableVariable
    set @Count = @Count + 1
end
user_55050850
Related