Drop a ##tmp table if it exists otherwise create it.

Question

Post reply

Drop a ##tmp table if it exists otherwise create it.

kp_it_simpl

SSC-Addicted

Points: 452
More actions
May 16, 2008 at 11:03 am

#191095

Here is what I am doing and obviously it never does the Else if the table does not exist with rows in it!!
If exists (select top 1 * from ##tmp_mj_jobSync)
Drop table ##tmp_mj_jobSync
Else
Select * into ##tmp_mj_jobSync from mj_jobSync
What can I replace "If exists (select top 1 * from ##tmp_mj_jobSync)" so that the Else statement will run????????????

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

This?

If exists (select top 1 * from ##tmp_mj_jobSync)

Drop table ##tmp_mj_jobSync

Select * into ##tmp_mj_jobSync from mj_jobSync

Actually, there is something better, but I have to find it... Stay Tune.

😎

kp_it_simpl SSC-Addicted Points: 452 More actions · Answer 2

Thanks Lynn. Yes I know there is something better and I am looking also. So I definitely will stay tuned.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

This is the way you should do it:

if exists(select object_id(N'tempdb.dbo.##tmp_mj_jobSync'))

drop table ##tmp_mj_jobSync

Select * into ##tmp_mj_jobSync from mj_jobSync

😎

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

You should avoid using global temp tables (##tablename), use local temp tables (#tablename).

This should a better way to check for a temp table. The other methods will have a problem if ##tmp_mj_jobSync is not a table, but some other temp object type, like a procedure.

if object_id(N'tempdb..#tmp_mj_jobSync','U') is not null

drop table #tmp_mj_jobSync

Select * into #tmp_mj_jobSync from mj_jobSync

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 5

I use the same method Michael, posted, as well. Works in 2k and 2k5 equally as well and avoids dipping system tables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

BEGIN TRY

drop table ##tmp_mj_jobSync

END TRY BEGIN CATCH END CATCH

Select * into ##tmp_mj_jobSync from mj_jobSync

This works also... 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]