Cannot reset temp SQL table

Question

Hi, I am creating a Temp table and inserting data into the table. I am going to use a Temp table to join a 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: the database already has an object named #Temp.

But when I run this DROP TABLE #MyTempTable , I get this error: the table "#Temp" cannot be deleted because it does not exist or you do not have permission. I am using SQL 2012

Answer 1

SELECT ... INTO ... the statement itself creates the #Temp table. This does not require a CREATE TABLE statement. Remove the "CREATE TABLE" statement and try.

Answer 2

You already have an object named "Temp" in your database. And you cannot delete this object due to access rights.

Answer 3

There is no need to drop the temporary table, as it is visible only before 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 is automatically deleted when the stored procedure completes.

OR

Please refer to this link for more temporary tables in SQL Server

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

Answer 4

You can create the same table twice in your code.

I had the same problem, I copied a section of code that I wanted to reuse (modify) in the same procedure, including the CREATE TABLE statement - I effectively created the table twice - and even though the CREATE TABLE statements were between separate markers BEGIN and END , and there was a DROP TABLE statement discarding the 1st “instance” before the 2nd CREATE statement, I ran into this exact error.