Can't drop temp table SQL

Question

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

Duplicate question - http://stackoverflow.com/questions/2642111/drop-table-fails-for-temp-table — Sandeep, Nov 05 '12 at 06:19
@Sandeep the answer suggests that the tempTable is created incorrectly — Johan de Klerk, Nov 05 '12 at 06:23

score 5 · Accepted Answer · answered Nov 05 '12 at 06:24

5

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

answered Nov 05 '12 at 06:24

Prasanna

4,583
2
22
29

score 2 · Answer 2 · answered Nov 05 '12 at 06:19

2

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

answered Nov 05 '12 at 06:19

Vivek

1,640
1
17
34

I still get the error even if I change the temp table to #MyTempTable. Updated in post – Johan de Klerk Nov 05 '12 at 06:24

Thangamani Palanisamy · Answer 3 · 2012-11-05T06:38:44.710

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/

score 0 · Answer 4 · edited Aug 18 '16 at 20:19

0

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.

edited Aug 18 '16 at 20:19

trincot

317,000
35
244
286

answered Aug 18 '16 at 19:53

user6732283

1

The accepted answer explains exactly why the error is happening. Your answer does not add anything useful to it. – trincot Aug 18 '16 at 20:20

score 0 · Answer 5 · answered Feb 24 '19 at 09:54

0

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

answered Feb 24 '19 at 09:54

Hrvoje

13,566
7
90
104

Can't drop temp table SQL

5 Answers5