-1

Wrote a very simple test case in SQLServer and don't quite understand why it doesn't work:

create table #temp(
  id int,
  val int)

insert into #temp values (1, 1), (2, 2)
select * from #temp

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

create table #temp(
  id int,
  val int)

insert into #temp values (1, 1), (2, 2)
select * from #temp
Aaron Bertrand
  • 242,666
  • 35
  • 420
  • 451
kchang
  • 93
  • 3
  • 10
  • post your error message... – chinna_82 Sep 13 '13 at 01:06
  • 3
    You haven't included any information about what is wrong here. You've said "it doesn't work" with no information about what that means. You obviously have a reason to think it "doesn't work". Is there a reason you're withholding that information from us? Please [edit] your question to include it. It's your task to explain the problem you're having so that we can try to help you solve it. – Ken White Sep 13 '13 at 01:10

2 Answers2

4

Please see the documentation, which states:

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

Your code yields the following error:

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

And this is not because the table has been dropped and can't be re-created; this code never gets executed, the parser actually sees you trying to create the same table twice (and it has no ability to see logic like your DROP command).

Other than using two different #temp table names, another workaround would be to just create the table once, and truncate it when you're done with your first bit of code.

Aaron Bertrand
  • 242,666
  • 35
  • 420
  • 451
1

This is a feature by design and is clarified by Microsoft against Microsoft Connect BugID 666430

A case study on the same issue is given at : temporary-table-could-not-be-re-created

Corey Adler
  • 15,151
  • 17
  • 63
  • 78
Anoo S Pillai
  • 36
  • 3