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 | |___________|_________________|
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.
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.
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.
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
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