CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Gerardo
Gerardo

Reputation: 369

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

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

Upvotes: 5

Views: 27341

Answers (7)

Rajesh Kumar Bhawsar
Rajesh Kumar Bhawsar

Reputation: 477

At first you should check if temp table is already exist if yes then delete it then create a empty table then use insert statement. refer below example.

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

SELECT TOP(0) Name , Address,PhoneNumber 
INTO #TmpTBL
FROM EmpDetail 

if @Condition=1
    INSERT INTO #TmpTBL (Name , Address,PhoneNumber) 
    SELECT Name , Address,PhoneNumber FROM EmpDetail;
else
    INSERT INTO #TmpTBL (Name , Address,PhoneNumber) 
    SELECT Name , Address,PhoneNumber FROM EmpDetail;

Upvotes: 0

RBerman
RBerman

Reputation: 391

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.

Upvotes: 10

Gabriel Marius Popescu
Gabriel Marius Popescu

Reputation: 2186

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

Upvotes: 1

saul
saul

Reputation: 979

This might be useful for someone else, keep in mind that If more than one temporary table is created inside a single stored procedure or batch, they must have different names. If you use the same name you won't be able to ALTER the PROCEDURE.

https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2012/ms174979(v=sql.110)#temporary-tables

Upvotes: 0

Jay Wheeler
Jay Wheeler

Reputation: 379

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.

Upvotes: 1

Donnie
Donnie

Reputation: 46923

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;

Upvotes: 10

M T Head
M T Head

Reputation: 1290

Add logic to delete if exists. Most likely you ran it previously. The table remains from the previous running of the stored procedure. If you log out and log in then run it, that would likely clear it. But the cleanest way is to check if it exists and delete it if it does. I assume this is MsSql.

Upvotes: 0

Related Questions