We help IT Professionals succeed at work.

Truncate temporary table

Medium Priority
8,638 Views
Last Modified: 2012-05-05
I'm sure i've done this before but I can't seem to find the answer;

How do I truncate a temporary table?

I've declared it, but I need to populate it and loop through it multiple times, each with the ID column starting at 0 so Delete from doesnt work.  Therefore I either need to truncate it or kill the variable and re-create it.
Any help would be much appreciated.

Regards,
Kinton
Comment
Watch Question

Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
CREATE TABLE #temp(i INT IDENTITY, j INT )
INSERT INTO #temp SELECT 1
SELECT * FROM #Temp
truncate TABLE #temp
SELECT * FROM #Temp

Author

Commented:
Oops, I may have used the wrong term.  I meant table variable i.e.

declare @allowedsets table (VarName varchar(255), tID int  identity (1,1))

Apologies.  Brain isn't working today!!!

Commented:
Truncating the table will not reset the identity column though, to do that, you'd have to do this:

truncate table #temp
DBCC CHECKIDENT('#temp', RESEED, 1)
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
You cannot truncate a table variable.

so you must replace it wth a temp tbale

Commented:
I don't know if you'll be able to truncate a table variable... you could give it a try, same syntax.  Also not sure if you'll be able to run checkident on a table variable, but worth a try.

If you can't, it might be possible for you to declare the table variable inside of a small block of code (between BEGIN and END statements), and, if your loop processing can be controlled outside of that block, then the variable will go out of scope and then get recreated each time through the loop, so, no need to truncate and reset the identity column.
CERTIFIED EXPERT

Commented:
If you need to use the table variable, and can't use a temp table instead, see the following post:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22161882.html

has a workaround for setting the identity values in the table after inserts, instead of using an identity column.

And just as a point of clarification, truncate table WILL reset the identity columns.

Commented:
You cannot truncate a table variable. That is one of the limitations.

Commented:
The DBCC CHECKIDENT() function did not work when I created a simple table variable. It looks as if there is a scope issue because the table variable only exists within the scope of the query.

Commented:
You could use aneeshattingal's advice and create a temporary table instead of a table variable. That would let you truncate and reseed the identity.

Author

Commented:
Thanks guys, after my post I found some other posts that said the same thing.

I ended up putting the whole thing in the temp table and querying it with criteria so it skipped the rows not needed on each pass.  The table isn't ever going to be large so it shouldnt cause a problem.

Thanks again for your help.

Regards,
Kinton