Why can I not reuse temp tables in T-SQL?

Question

Here's some sample code:

if object_id('tempdb..#TempList') is not null drop table #TempList

create table #TempList (
   ID int,
   Name varchar(20)
)

insert into #TempList values (1, 'Alpha')
insert into #TempList values (2, 'Beta')
insert into #TempList values (3, 'Gamma')
insert into #TempList values (4, 'Delta')
insert into #TempList values (5, 'Omega')

select * from #TempList

if object_id('tempdb..#TempList') is not null drop table #TempList
drop table #TempList

create table #TempList (
   ID_New int,
   AnotherID int,
   Name_New varchar(40)
)

insert into #TempList values (100, 110, 'Orange')
insert into #TempList values (101, 111, 'Red')
insert into #TempList values (102, 112, 'Purple')
insert into #TempList values (103, 113, 'Blue')
insert into #TempList values (104, 114, 'Green')

select * from #TempList

This yields the following error:

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

Am I not able to re-use the same temp table name within the same SQL script? Is there a way to re-use the same temp table name?

Thanks.

Answer 1

Change

if object_id('tempdb..#TempList') is not null drop table #TempList
drop table #TempList

create table #TempList (

to

if object_id('tempdb..#TempList') is not null drop table #TempList
drop table #TempList

GO;

create table #TempList (

SQL Server query optimizer gets confused.

It sees that you create the same table second time.
Note that table drop (as well as create) has probabilistic outcome,
the result will be known only at run-time (not upon syntax control or query exec plan creation aka "compilation" in jargon of SQL Server)

Answer 2

Seems like a parser error to me. I'm betting it sees the two create table statements creating the same table and throws the error, regardless of the drop statement. Breaking it up with a go statement works fine though.

create table #Temp (ID int)
insert into #Temp (ID)
select 1 union all select 2

select ID from #Temp

drop table #Temp
go
create table #Temp (ID int)

insert into #Temp (ID)
select 10 union all select 11

select ID from #Temp

drop table #Temp