Temporary table in SQL server causing ' There is already an object named' error
You are dropping it, then creating it, then trying to create it again by using SELECT INTO
. Change to:
DROP TABLE #TMPGUARDIAN
CREATE TABLE #TMPGUARDIAN(
LAST_NAME NVARCHAR(30),
FRST_NAME NVARCHAR(30))
INSERT INTO #TMPGUARDIAN
SELECT LAST_NAME,FRST_NAME
FROM TBL_PEOPLE
In MS SQL Server you can create a table without a CREATE TABLE
statement by using SELECT INTO
There is already an object named '#tmptable' in the database
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;
ERROR: There is already an object named '#temp' in the database
Like Larnu already said, this is a parsing error. It sees you trying to create the same table multiple times and doesn't know that only one of them can be reached.
Depending on how this temp table is being used there might be better ways to refactor the code but working from what was provided you have a couple of options:
- Use dynamic queries to hide the SQL from the parser. Dynamic queries are generally frowned upon and should be avoided when possible. (This has been updated to use a global temp table to work around the scope issue)
IF 1=1
EXEC('SELECT
[col1] = ''inside IF 1'',
[col2] = ''inside IF 2''
INTO
##temptable')
ELSE
EXEC('SELECT
[col1] = ''inside Else 1'',
[col2] = ''inside Else 2'',
[col3] = ''inside Else 3''
INTO
##temptable')
- If you know ahead of time all of the column names then you can
CREATE
the#temptable
with all possible columns, and in your conditional logic you can use ALTER TABLE to remove unused columns.
CREATE TABLE #temptable (
col1 varchar(10),
col2 varchar(10),
col3 varchar(10)
)
IF 1 = 1
BEGIN
INSERT INTO #temptable (col1, col2)
SELECT
col1 = 'inside IF 1',
col2 = 'inside IF 2'
ALTER TABLE #temptable DROP COLUMN col3
END
ELSE
BEGIN
INSERT INTO #temptable (col1, col2, col3)
SELECT
col1 = 'inside Else 1',
col2 = 'inside Else 2',
col3 = 'inside Else 3'
END
drop temp table but already founded 'There is already an object named '#temp' in the database'
Just change out your logic flow a little. If all that matters is that #temp
is empty when the INSERT
happens, this should do what you need.
create table #temp (id int)
create table #swap (id int)
<Add loop logic here>
truncate table #temp
insert #temp(id)
select id from #swap
<Close out loop logic>
drop table #swap
drop table #temp
I also made the column names explicit. SELECT *
is an accident waiting to happen in production code.
There is already an object named '##Temp' in the database
You should re-write your stored proc to drop the temp table if it exists, then you won't ever have this issue
IF (SELECT object_id('TempDB..##Temp')) IS NOT NULL
BEGIN
DROP TABLE ##Temp
END
Stored Procedure: There is already an object named '#columntable' in the database
As per SQL Server specification, it is not allowed. Please refer to the documentation.
If more than one temporary table is created inside a single stored
procedure or batch, they must have different names.
You are creating two temporary tables, with the same name #dbreviews
. This is not allowed.
Related Topics
How to Avoid "Table Mutating" Errors
Odata Case In-Sensitive Filtering in Web API
Suppress Output of Variables Substitution in SQLplus
Access 2007: "Select Count(Distinct ..."
How to Group by the Each Week Upto Last Six Week Sundays Dates in SQL
Sql-Only Find Time and Not Date in Access Date/Time Field
Lock Escalation - What's Happening Here
How to Update in SQLite Using a Left Join to Select Candidate Rows
How to Use Merge on Linked Servers
Select Top Distinct Results Ordered by Frequency
Combine Multiple Rows into Multiple Columns Dynamically in SQL Server
How to Update Ms Access Database Table Using Update and Sum() Function
Findout Duplicate Rows in a Table While Inserting
Postgresql - Query from Bash Script as Database User 'Postgres'