Why can't I reuse temporary tables in T-SQL?

Question

Here is a 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 results in the following error:

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

Can't I reuse the same temp table name in the same SQL script? Is there a way to reuse the same temp table name?

Thank.

Answer 1

Edit

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 is confusing.

, .
, ( ) ,
( aka "" SQL Server)

Answer 2

. , , create table , drop. go .

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

Answer 3

#temp. #temp, .

.

You need to run another query using a comparison LIKEto delete the table #temp:

IF EXISTS (
SELECT *
FROM sys.tables
WHERE name LIKE '#templist%')
DROP TABLE #templist
CREATE TABLE #templist...

Answer 4

Yes. Leave the table when you finish using it. :)