Search code examples
sql-serversql-server-2012temp-tables

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.

Solution

  • 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))