Clearing Temp tables

Question

Post reply

Clearing Temp tables

patelxx

SSC Eights!

Points: 853
More actions
June 6, 2015 at 3:44 am

#301246

I am just learning about temp temps using Iteration, how do I clear the contents of the #temp table because when I re-run the query I get the following error:
Msg 2714, Level 16, State 6, Line 6
There is already an object named '#mytemp2' in the database.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

At the end of your batch, run DROP TABLE, then you won't get an error when you create it at the beginning.

Or, comment out the CREATE TABLE and TRUNCATE TABLE <temp table name> at the end

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

patelxx (6/6/2015)
I am just learning about temp temps using Iteration, how do I clear the contents of the #temp table because when I re-run the query I get the following error:
Msg 2714, Level 16, State 6, Line 6
There is already an object named '#mytemp2' in the database.

Clearing the contents won't solve this problem. The Temp Table would need to be dropped in each iteration or, if you want to reuse the Temp Table in an iteration, build it outside the loop and truncate it in the loop if it needs to be cleared for each iteration.

My real concern is that you're new and your also talking about "using Iteration". That could be a serious problem or not. What is it that you're trying to do that uses "Iteration"? And, any chance of you posting the code so we can see if we can help in that area?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)