CODEWITHSUNDEEP
sql-server-2012temp-tables
user3082259
user3082259

Reputation: 37

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

Upvotes: 0

Views: 94

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

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.

Upvotes: 2

Related Questions