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

Question

I´m trying to execute stored procedure but I get an issue of an existing temporal table, but I just create one time and use into another part of code

SELECT ...
INTO #tmpUnidadesPresupuestadas 
FROM proce.table1 

--Insertar in table src..
INSERT INTO table (
 ....) 
SELECT
....
FROM
    #tmpUnidadesPresupuestadas

I get this message:

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

How can I solve it? Regards

Answer 1

A temp table lives for the entirety of the current session. If you run this statement more than once, then the table will already be there. Either detect that and truncate it, or before selecting into it drop it if it exists:

DROP TABLE IF EXISTS #tmpUnidadesPresupuestadas

If prior to SQL Server 2016, then you drop as such:

IF OBJECT_ID('tempdb.dbo.#tmpUnidadesPresupuestadas', 'U') IS NOT NULL
  DROP TABLE #tmpUnidadesPresupuestadas; 

Answer 2

Without seeing more of the code, it's not possible to know if the following situation is your problem, but it could be.

When you have mutually exclusive branches of code that both do a SELECT...INTO to the same temp table, a flaw causes this error. SELECT...INTO to a temp table creates the table with the structure of the query used to fill it. The parser assumes if that occurs twice, it is a mistake, since you can't recreate the structure of the table once it already has data.

if @Debug=1
    select * into #MyTemp from MyTable;
else
    select * into #MyTemp from MyTable;

While obviously not terribly meaningful, this alone will show the problem. The two paths are mutually exclusive, but the parser thinks they may both get executed, and issues the fatal error. You extend that, wrapping each branch in a BEGIN...END, and add the drop table (conditional or not) and the parser will still give the error.

To be fair, in fact both paths COULD be executed, if there were a loop or GOTO so that one time around @Debug = 1, and the other time it does not, so it may be asking too much of a parser. Unfortunately, I don't know of a workaround, and using INSERT INTO instead of SELECT INTO is the only way I know to avoid the problem, even though that can be terribly onerous to name all the columns in a particularly column-heavy query.

Answer 3

I am a bit unclear as to what you are attempting. I assume you don't want to drop the table at this point. I believe the syntax you may be looking for is Insert Into

Insert into #tmpUnidadesPresupuestadas (Col1, col2, ... colN)
Select firstcol, secondcol... nthCol 
From Data

If you do indeed wish to drop the table, the previous answers have that covered.

Answer 4

Make sure the stored procedure and the table doesn't have same name.