Search code examples
sql-server-2012temp-tables

Anomalous behavior in SQL Server Temp tables

I don't understand why the below code returns the error

Msg 2714, Level 16, State 1, Line 16
There is already an object named '#temptable' in the database.

If lines 1-5 are executed separately followed by lines 6-8, it works fine. I think I am missing something fundamental about how SQL Server processes the queries. Request one of the gurus to shed some light on this issue. 

select top 10 col1, col2
into #temptable
from tbl

if object_id('tempdb..#temptable') is not null
   drop table #temptable

select top 10 col1, col2
into #temptable
from tbl
Solution

  • You code fails when the batch is compiled not when it is executed.

    The behavior is documented in CREATE TABLE

    If more than one temporary table is created inside a single stored procedure or batch, they must have different names.