CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Jin Ho
Jin Ho

Reputation: 3665

Temporary table in SQL Server - error occurs if the query takes long time to complete

Have a look a the following query.

select * 
into TempTable
from MainTable

select * from TempTable
WAITFOR DELAY '00:00:10'

drop table TempTable

After executing this query, I open other window and execute the query:

select * 
into TempTable
from MainTable

select * from TempTable

drop table TempTable

I get the following error:

There is already an object named 'TempBucket' in the database.

Suppose that it a part of the stored procedure, and it takes a long time to finish. If there's a second call to this stored procedure, this error will occur. How do I fix this error?

Upvotes: 2

Views: 1451

Answers (2)

Devart
Devart

Reputation: 121902

Try this one -

IF OBJECT_ID (N'tempdb.dbo.#TempTable', 'U') IS NOT NULL
   DROP TABLE #TempTable

SELECT *
INTO #TempTable
FROM dbo.MainTable

SELECT *
FROM #TempTable

Upvotes: 3

Drew R
Drew R

Reputation: 3088

I assume you are using MSSQL DBMS by the tags against your post. Use a genuine temp table: prefix the name of the table with '#'. Using this method the temp table will exist only in the scope of the procedure within which it was created. 

select * 
into #TempTable
from MainTable

select * from #TempTable

No drop actually neccessary but is probably better practice.

Upvotes: 4

Related Questions