Menu
Is it possible to truncate or flush out a table variable in SQL Server 2008?
declare @tableVariable table ( id int, value varchar(20) ) while @start <= @stop begin insert into @tableVariable(id, value) select id , value from xTable where id = @start --Use @tableVariable --@tableVariable should be flushed out of -- old values before inserting new values set @start = @start + 1 end 
855
You can just use DELETE FROM @tableVariable , as described in the accepted answer, to get functionality substantially equivalent to TRUNCATE TABLE (except for the logging - this could certainly be a problem if there were a lot of rows in the variable, or the SQL that created the variable was being run very often).
you can't truncate a table variable. you could either use a temp table or just simply create a new table variable and use it and just let them both fall out of scope at the end.
Usage. The DROP VARIABLE statement eliminates a SQL variable that was previously created using the CREATE VARIABLE statement. Variables are automatically eliminated when the database connection is released.
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.
just delete everything
DELETE FROM @tableVariable No, you cannot TRUNCATE a table variable since it is not a physical table. Deleting it would be faster. See this answer from Aaron Bertrand.
22
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
