Can't drop temp table SQL

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


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


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/

Tags:

Sql

Recent Posts