Error "There is already an Object named '#TEMP' in the database"

Asked

Viewed 3,164 times

4

I’m performing a load, using a temporary table on Microsoft SQL Server 2008.

SELECT CAMPO1,
        CAMPO2,
        CAMPO3,
        CAMPO4 
     INTO #TEMP 
     FROM DADOS

In the query, I use the following command, to check if the temporary table exists, if the table exists DROP TABLE, as the example below.

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

After execution the table is still created in the bank tempdb

If I execute only: 

DROP TABLE #TEMP

The message is resumed:

Msg 3701, Level 11, State 5, Line 1

Cannot drop the table '#TEMP', because it does not exist or you do not have permission.

It is only possible to perform new load only after disconnecting from the server.

I ask for help.

sql-server

  • Stupid question, but it needs to be answered. The login you are allowed to use DROP?

  • Try using ##TEMP, with 2 #

  • 1

    The #TEMP table when using only one # is a section temp, make sure that all commands executed for your #TEMP are in the same section of SQL Server.

  • Always use ## in the temporary tables if you need to delete them from another session. I believe this might be it. And also after the second point you need to pass the bank, for example 'tempdb.dbo. #TEMP'. I hope it helps you, strong hug! Att., Wilson Gomes

1 answer

0

Try the code below

IF EXITS( SELECT TOP 1 * FROM tempdb..#TEMP)

BEGIN

DROP TABLE #TEMP

END

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.