There is already an object named '#xxxx' in the database

Question

I'm dropping/creating a temp table many times in a single script

IF OBJECT_ID('tempdb..#uDims') IS NOT NULL
DROP TABLE #uDims

select * into #uDims from table1

.... do something else 

IF OBJECT_ID('tempdb..#uDims') IS NOT NULL
DROP TABLE #uDims

select * into #uDims from table2 -- >> I get error here

.... do something else 

IF OBJECT_ID('tempdb..#uDims') IS NOT NULL
DROP TABLE #uDims

select * into #uDims from table3  -- >> and here

.... do something else 

when trying to run the script, I get

There is already an object named '#uDims' in the database.

on the second and third "select into..."

That is obviously a compile time error. If I run the script section by section, every thing will work well.

There are many workaround for this issue, but I want to know why SSMS is upset on that.

Answer 1

You can't create the same temp table more than once inside a stored procedure.

Per the documentation (in the Remarks section),

If more than one temporary table is created inside a single stored procedure or batch, they must have different names.

So, you either have to use different temp table names or you have to do this outside a stored procedure and use GO.

Answer 2

Ivan Starostin is correct. I test on my SQL this TSQL and it works fine.

IF OBJECT_ID('tempdb..#uDims') IS NOT NULL
DROP TABLE #uDims
select top 10 *  into #uDims from tblS
go 
IF OBJECT_ID('tempdb..#uDims') IS NOT NULL
DROP TABLE #uDims
select top 10 * into #uDims from Waters

without the go I get the same error as you(FLICKER).