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
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.
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.
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;