There is already an object named ‘#temp’ in the database

Generally the error

There is already an object named ‘#temp’ in the database occurs when we try to create/use temp table in our query.


Reasons:

The Temp table with the name already exists already in the database.


Solution:

1. Check Whether the table with the name temp already exising in the database.

2. If so drop the table and then create the temp table.

Example


IF EXISTS (
SELECT *
FROM sys.tables
WHERE name LIKE '#temp%')
DROP TABLE #temp
CREATE TABLE #temp(ID INT, FileName varchar(500) )


Note:

Must use LIKE '#temp%' .

We should not use WHERE name ='#temp'


3. In Sql server 2005 0r 2008 we can use try catch block

begin try
DROP TABLE #temp
end try

begin catch

end catch

CRETAE TABLE #temp (ID INT, FileName varchar(500)



4. We should not be run the above code in Temp Database.