CODEWITHSUNDEEP
sql
Johan de Klerk
Johan de Klerk

Reputation: 2535

Can't drop temp table SQL

Hi Im creating a Temp table and inserting data to the Table. Im going to use the Temp table to Join it to the specific User. 

CREATE TABLE #MyTempTable
(
    UsersId int,
    ValautionCount int 
)

    SELECT
        U.UserId, 
        COUNT(*) AS ValautionCount
    INTO  #MyTempTable
    FROM 
        Users U
        Right JOIN Valuation V ON V.ValuationUser = U.UserId
    GROUP BY 
        U.UserId



DROP TABLE #MyTempTable

When I run this query I get this error : There is already an object named '#Temp' in the database.

But when I run this query DROP TABLE #MyTempTable I get this error: Cannot drop the table '#Temp', because it does not exist or you do not have permission. Im using SQL 2012

Upvotes: 6

Views: 20722

Answers (5)

Hrvoje
Hrvoje

Reputation: 15152

Official answer from Microsoft page on how to drop temp table helped me. In short I used this and it worked fine.

use tempdb
go

IF OBJECT_ID(N'tempdb..#yourtemptable', N'U') IS NOT NULL   
DROP TABLE #yourtemptable;  
GO

Using MS SQL 2017

Upvotes: 0

user6732283
user6732283

Reputation: 1

You may be creating the same table twice in your code.

I experienced the same problem, I had copied a section of code I wanted to reuse (modified) into the same procedure, including a CREATE TABLE statement - I was effectively creating the table twice - and even though the CREATE TABLE statements were between separate BEGIN and END markers, and there was a DROP TABLE statement dropping the 1st 'instance' before the 2nd CREATE statement, I encountered this exact error.

Upvotes: 0

Thangamani Palanisamy
Thangamani Palanisamy

Reputation: 5290

No need to drop the temp table since it visible at only till the session.

Create PROCEDURE proctemptable
BEGIN

IF object_id('tempdb..#Temp') is not null  // Try this hope this will work
BEGIN
  DROP TABLE #Temp
END

CREATE TABLE #Temp
(
    UsersId int,
    ValautionCount int 
)

SELECT
    U.UserId, 
    COUNT(*) AS ValautionCount
INTO  #Temp
FROM 
    Users U
    Right JOIN Valuation V ON V.ValuationUser = U.UserId
GROUP BY 
    U.UserId

//DROP TABLE #Temp 

END

No need to drop the #Temp table, it will drop it automatically when the stored procedure execution completed

OR

Please refer this link for more temp tables in sql server

http://www.simple-talk.com/sql/t-sql-programming/temporary-tables-in-sql-server/

Upvotes: 2

Prasanna
Prasanna

Reputation: 4703

SELECT ... INTO ... statement itself create the #Temp table. Does not need CREATE TABLE statement here. Remove "CREATE TABLE" statement and try.

Upvotes: 5

Vivek
Vivek

Reputation: 1680

You already have an entity by name "Temp" in your database. And you are not able to drop that entity because of access permissions.

Upvotes: 2

Related Questions