Temporary table in SQL server causing ' There is already an object named' error

Question

I have the following issue in SQL Server, I have some code that looks like this:

DROP TABLE #TMPGUARDIAN
CREATE TABLE #TMPGUARDIAN(
LAST_NAME NVARCHAR(30),
FRST_NAME NVARCHAR(30))  

SELECT LAST_NAME,FRST_NAME INTO #TMPGUARDIAN  FROM TBL_PEOPLE

When I do this I get an error 'There is already an object named '#TMPGUARDIAN' in the database'. Can anyone tell me why I am getting this error?

score 102 · Accepted Answer · answered Aug 19 '13 at 19:09

102

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

answered Aug 19 '13 at 19:09

Hart CO

34,064
6
48
63

1

I am using SELECT INTO rather than CREATE TABLE, and still getting the error `There is already an object named '#MyTempTable' in the database.` – naz786 Sep 16 '21 at 09:53
1

@naz786 In that case it must already exist, for temp tables you might find it helpful to include a `DROP TABLE IF EXISTS #yourTable;` before you create. – Hart CO Sep 16 '21 at 13:59
Yes I tried that but didn't work. Since this SELECT INTO was not working for me, I have resorted to writing two queries: one for creating the table and another for inserting into the table. This was a workaround for me. Thank you anyway. :) – naz786 Sep 17 '21 at 11:06

granadaCoder · Answer 2 · 2022-11-11T14:00:57.657

I usually put these lines at the beginning of my stored procedure, and then at the end.

It is an "exists" check for #temp tables.

IF OBJECT_ID('tempdb..#MyCoolTempTable') IS NOT NULL
begin
        drop table #MyCoolTempTable
end

Full Example:

(Note the LACK of any "SELECT INTO" statements)

CREATE PROCEDURE [dbo].[uspTempTableSuperSafeExample]
AS
BEGIN
    SET NOCOUNT ON;


    IF OBJECT_ID('tempdb..#MyCoolTempTable') IS NOT NULL
    BEGIN
            DROP TABLE #MyCoolTempTable
    END


    CREATE TABLE #MyCoolTempTable (
        MyCoolTempTableKey INT IDENTITY(1,1),
        MyValue VARCHAR(128)
    )  


    INSERT INTO #MyCoolTempTable (MyValue)
        SELECT LEFT(@@VERSION, 128)
        UNION ALL SELECT TOP 3 LEFT(name, 128) FROM sysobjects       

    INSERT INTO #MyCoolTempTable (MyValue)
        SELECT TOP 3 LEFT(name, 128) FROM sysobjects ORDER BY NEWID()

    ALTER TABLE #MyCoolTempTable 
        ADD YetAnotherColumn VARCHAR(128) NOT NULL DEFAULT 'DefaultValueNeededForTheAlterStatement'

    INSERT INTO #MyCoolTempTable (MyValue, YetAnotherColumn)
       SELECT TOP 3 LEFT(name, 128) , 'AfterTheAlter' FROM sysobjects ORDER BY NEWID()


    SELECT MyCoolTempTableKey, MyValue, YetAnotherColumn FROM #MyCoolTempTable



    IF OBJECT_ID('tempdb..#MyCoolTempTable') IS NOT NULL
    BEGIN
            DROP TABLE #MyCoolTempTable
    END


    SET NOCOUNT OFF;
END
GO

Output ~Sample:

1   Microsoft-SQL-Server-BlahBlahBlah DefaultValueNeededForTheAlterStatement

2   sp_MSalreadyhavegeneration  DefaultValueNeededForTheAlterStatement

3   sp_MSwritemergeperfcounter DefaultValueNeededForTheAlterStatement

4   sp_drop_trusted_assembly    DefaultValueNeededForTheAlterStatement

5   sp_helplogreader_agent  DefaultValueNeededForTheAlterStatement

6   fn_MSorbitmaps  DefaultValueNeededForTheAlterStatement

7   sp_check_constraints_rowset DefaultValueNeededForTheAlterStatement

8   fn_varbintohexstr   AfterTheAlter

9   sp_MSrepl_check_publisher   AfterTheAlter

10  sp_query_store_consistency_check    AfterTheAlter

Also, see my answer here (on "what is the SCOPE of a #temp table") : https://stackoverflow.com/a/20105766/214977

In my case it seems it's not enough. The mere fact that I am write twice in the same select the same "SELECT MyField INTO #TempTable" is enough to cause the compilation error. The two select into cannot be hit at the same time because they are in different branches of a IF THEN ELSE: nonetheless, i can't write twice the same SELECT INTO — Johannes Wentu, Feb 03 '16 at 09:59
Don't use Select-Into. Its a "short cut". Create the #tempTable, use the "Insert Into #MyTemp Select Col1, Col2 from dbo.MyTable" syntax. — granadaCoder, Mar 14 '16 at 20:15
IF OBJECT_ID('tempdb..#TMPGUARDIAN') IS NOT NULL begin drop table #TMPGUARDIAN end Select LAST_NAME,FRST_NAME INTO #TMPGUARDIAN from TBL_PEOPLE SELECT * FROM #TMPGUARDIAN — AMRESH PANDEY, Apr 07 '18 at 06:50
Amresh, that doesn't work in this case - as noted elsewhere, even when the select-into is in mutually exclusive branches, the mere fact there are two of them into the same temp table will cause a compiler error. It's a deficiency in the lexxer, apparently. — RBerman, Apr 16 '19 at 15:32

score 9 · Answer 3 · answered Aug 19 '13 at 19:21

You must modify the query like this

CREATE TABLE #TMPGUARDIAN(
LAST_NAME NVARCHAR(30),
FRST_NAME NVARCHAR(30))  

INSERT INTO #TMPGUARDIAN(FRST_NAME,LAST_NAME)
SELECT LAST_NAME,FRST_NAME  FROM TBL_PEOPLE

-- Make a last session for clearing the all temporary tables. always drop at end. In your case, sometimes, there might be an error happen if the table is not exists, while you trying to delete.

DROP TABLE #TMPGUARDIAN

Avoid using insert into Because If you are using insert into then in future if you want to modify the temp table by adding a new column which can be filled after some process (not along with insert). At that time, you need to rework and design it in the same manner.

Use Table Variable http://odetocode.com/articles/365.aspx

declare @userData TABLE(
 LAST_NAME NVARCHAR(30),
    FRST_NAME NVARCHAR(30)
)

Advantages No need for Drop statements, since this will be similar to variables. Scope ends immediately after the execution.

Kalaivani Mathivanan · Answer 4 · 2014-12-22T07:40:04.363

Some times you may make silly mistakes like writing insert query on the same .sql file (in the same workspace/tab) so once you execute the insert query where your create query was written just above and already executed, it will again start executing along with the insert query.

This is the reason why we are getting the object name (table name) exists already, since it's getting executed for the second time.

So go to a separate tab to write the insert or drop or whatever queries you are about to execute.

Or else use comment lines preceding all queries in the same workspace like

CREATE -- …
-- Insert query
INSERT INTO -- …

score 0 · Answer 5 · answered Sep 28 '22 at 08:03

I found I had the same problem with:

DROP TABLE IF EXISTS #MyTempTable
CREATE TABLE #MyTempTable (
    MyTempTableID INT,
    OtherColID INT
);

But I was able to solve it by separating the statements with a GO.

DROP TABLE IF EXISTS #MyTempTable
GO
CREATE TABLE #MyTempTable (
    MyTempTableID INT,
    OtherColID INT
);

score -1 · Answer 6 · answered Jan 15 '19 at 08:36

-1

In Azure Data warehouse also this occurs sometimes, because temporary tables created for a user session.. I got the same issue fixed by reconnecting the database,

answered Jan 15 '19 at 08:36

dgcharitha

333
3
13

That "fixed" it because the disconnect deleted the temp table. This has nothing to do with the original question. – RBerman Apr 16 '19 at 15:33

Temporary table in SQL server causing ' There is already an object named' error

6 Answers6

Linked