CODEWITHSUNDEEP
sql-serversql-server-2012temp-tables
UnDiUdin
UnDiUdin

Reputation: 15374

Reason why a Temp Table seems to extend its scope in sql server

I faced a strange situation

i use a temp table created with the select into technique like

select *
into #MyTempTable
from TABLE

In principle the scope pf #MyTempTable is limited to the process id.

but even if i explicitly use 

drop table #MyTempTable

when i run again 

select *
into #MyTempTable
from TABLE

i have an error that temp table already exists.

Why this can occur? I never faced this issue before, i use this technique since years.

Upvotes: 1

Views: 50

Answers (1)

Rich Benner
Rich Benner

Reputation: 8113

Are you definitely running the code in the same window? If you're dropping the table in another window it won't drop it properly.

I always consider it best practice to run code that checks if it exists before explicitly creating the table (rather than SELECT INTO). Something like this;

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL DROP TABLE #TempTable
CREATE TABLE #TempTable (RowNumber int identity(1,1), OtherField nvarchar(max))

Upvotes: 4

Related Questions