CODEWITHSUNDEEP
sqlsql-servert-sqltemp-tables
Art F
Art F

Reputation: 4202

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

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?

Upvotes: 88

Views: 304743

Answers (6)

granadaCoder
granadaCoder

Reputation: 27842

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

Upvotes: 54

Scotty
Scotty

Reputation: 33

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

Upvotes: 1

dgcharitha
dgcharitha

Reputation: 345

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,

Upvotes: 0

Kalaivani Mathivanan
Kalaivani Mathivanan

Reputation: 39

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 -- …

Upvotes: 3

kbvishnu
kbvishnu

Reputation: 15630

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.

Upvotes: 9

Hart CO
Hart CO

Reputation: 34774

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

Upvotes: 110

Related Questions