Truncate temporary table

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
LVL 2
kintonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
CREATE TABLE #temp(i INT IDENTITY, j INT )
INSERT INTO #temp SELECT 1
SELECT * FROM #Temp
truncate TABLE #temp
SELECT * FROM #Temp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kintonAuthor 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!!!
0
mdouganCommented:
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)
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Aneesh RetnakaranDatabase AdministratorCommented:
You cannot truncate a table variable.

so you must replace it wth a temp tbale
0
mdouganCommented:
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.
0
Snarf0001Commented:
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.
0
ptjcbCommented:
You cannot truncate a table variable. That is one of the limitations.

0
ptjcbCommented:
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.
0
ptjcbCommented:
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.

0
kintonAuthor 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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.