TmpTbl with # sign

Transact-SQL
1

Hello,

I have a simple query listed below:

select *
into #TmpTbl
from [Person].[Address]

select * from #TmpTbl

drop table #TmpTbl

I am wondering if there is a way in sql that automate the drop table#TmpTbl if I stop in the middle of the execution process? So I dont have to highlight drop table #TmpTbl
and run to clear this below error msg:

Msg 2714, Level 16, State 6, Line 1
There is already an object named '#TmpTbl' in the database.

Highlight one TmpTbl is not an issue to clear that error but imagine if I have more than 10 tmptbls? Is that a way I add a code to handle this?

2

write:

drop table #TmpTbl

select *
into #TmpTbl
from [Person].[Address]

select * from #TmpTbl

Then execute all three statements each time. The first time the drop will fail, but that doesn't matter.

1 Like
3

Put this at the beginning of your code

IF OBJECT_ID('tempdb..#TmpTbl') IS NOT NULL drop table #TmpTbl;
2 Likes
4

Thanks gbritton and JamesK. Both additional code and concepts work!

5

Hi JamesK, how to I add second tmptbl, third and so on if I have more than one tmptbl? Thanks JamesK

6

I got it JamesK. I just added additional if statement. Thanks JamesK

7

Hi JamesK,

Question. What is the .. inside the ('tempdb..#TmpTbl') represent for? I am trying to understand the logic.

IF OBJECT_ID('tempdb..#TmpTbl') IS NOT NULL drop table #TmpTbl;

does it mean, if the object inside the tempdb folder where if there is a #TmpTbl called #TmpTbl is NOT NULL, in other word, exist then drop it. Is the .. just the format in SQL instead (, - comma for example?) I am just curious. Thanks James.

8

Unlike base tables, all temp tables (the ones whose name starts with a #) are created in the tempdb database. tempdb..#TmpTbl allows you to refer to the the temp table named #TmpTbl using the 3-part naming convenion.

OBJECT_ID function lets you retrieve the id of an object. If there is no object that matches the name you pass in, it returns null.