Drop temp table issue

Question

I have a problem while drop temp table. If run below query in 1 time its show error

There is already an object named '#a' in the database.

Please run query in 1 time

if(OBJECT_ID('tempdb..#a','U')Is not null)
Begin
drop table #a
End

Create table #a
(
id int
)

insert into #a
select 1

select * from #a


if(OBJECT_ID('tempdb..#a','U')Is not null)
Begin
drop table #A
End

Create table #a
(
id int
,name varchar(10)
)

insert into #a
select 2,'name'

select * from #A

What RDBMS ist this? SQL Server? You are probably missing a `GO` right before the second `if` — Ocaso Protal, May 11 '17 at 06:34
@PawełTajs "Association bonus" maybe? I can see the "Your answer" box at the end of the page. Seems like a question for MSO. — Ocaso Protal, May 11 '17 at 09:35

Shushil Bohara · Answer 1 · 2017-05-11T07:11:18.567

1

TRY THIS: you have to use GO to make a batch and execute each separately because you are creating same temporary table again and it's executing it at once:

if(OBJECT_ID('tempdb..#a','U')Is not null)
Begin
    drop table #a
End
GO
Create table #a (id int)

insert into #a
select 1

select * from #a

if(OBJECT_ID('tempdb..#a','U')Is not null)
Begin
    drop table #a
End
GO
Create table #a(id int,name varchar(10))
insert into #a
select 2,'name'

select * from #a

NOTE: Please try to use the similar CASE for the table name

edited May 11 '17 at 07:11

answered May 11 '17 at 06:36

Shushil Bohara

5,556
2
15
32

`GO` separates *batches*, not *transactions*. – Damien_The_Unbeliever May 11 '17 at 06:47
Corrected, Thanks buddy. – Shushil Bohara May 11 '17 at 07:10

score 0 · Answer 2 · answered May 11 '17 at 06:47

You miss 'GO' statement.

Without 'GO' in between, the whole thing will be considered as one single script and when the select statement looks for the column, it won't be found.

With 'GO', it will consider the part of the script up to 'GO' as one single batch and will execute before getting into the query after 'GO'.

if(OBJECT_ID('tempdb..#a','U')Is not null)
Begin
drop table #a
End
go
Create table #a
(
 id int
 )

insert into #a
select 1

select * from #a


if(OBJECT_ID('tempdb..#a','U')Is not null)
Begin
drop table #a
End

go

Create table #a
(
 id int
 ,name varchar(10)
)

insert into #a
select 2,'name'

select * from #a

Please mark it as answer if it's useful.

Thank you very much for quick response. – Sam May 11 '17 at 06:51 — Sam, May 11 '17 at 06:51

Drop temp table issue

2 Answers2