Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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     |        
|___________|_________________|
like image 306
Vaccano Avatar asked Jun 24 '11 17:06

Vaccano


People also ask

Can we reset identity column in SQL Server?

Now truncate the Employee table to delete all rows. To reset the identity column, you have to truncate the table. TRUNCATE table Employee; The above statement will delete all the records from the Employee table.

Does truncating a table reset the identity?

Truncate command reset the identity to its seed value. It requires more transaction log space than the truncate command. It requires less transaction log space than the truncate command. You require Alter table permissions to truncate a table.

Can you alter a table variable?

You cannot alter the definition of a table variable once it is declared. This means that everything you need in the table definition must be included in the original DECLARE statement.


1 Answers

Instead relying on an Identity, why not use the new ranking functions such as Row_Number

Insert @MyTableVar( Id, Value )
Select Row_Number() Over ( Order By Value )
    , Value
From SomeOtherTable
like image 142
Thomas Avatar answered Oct 24 '22 11:10

Thomas