deleting # NOT ##temp tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

deleting # NOT ##temp tables

Author

Topic

heze
Posting Yak Master

192 Posts

Posted - 2006-04-09 : 00:30:05

Hi how can I delete a local temp table?
I know a golbal is deleted by
if exists(select * from tempdb..sysobjects where name='##MyTemGlobalTable' and type='U')drop table ##MyTemGlobalTable'

but how do I drop a #MyTemGlobalTable' ?

thank you

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-09 : 01:26:53

drop table #MyLocalTempTable'

CODO ERGO SUM

heze
Posting Yak Master

192 Posts

Posted - 2006-04-09 : 01:29:02

thanks Michael, but this gives an error message if #MyLocalTempTable does not exist,
I am precisely trying to avoid this message

thank u

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-09 : 01:31:55

Don't try to drop if it doesn't exist.

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-09 : 01:33:40

[code]if exists (select * from tempdb..sysobjects where id = object_id('tempdb..##global')) drop table ##global

create table ##global
(
a int
)

if exists (select * from tempdb..sysobjects where id = object_id('tempdb..#local')) drop table #local

create table #local
(
b int
)[/code]

heze
Posting Yak Master

192 Posts

Posted - 2006-04-09 : 01:36:25

The script Im writting requires that no human intervention be done, so you mean, if the script executes one thousand times, I have to stop everything, and check if the table exists one thousand times? I think I would not be able to afford the rent if I provided such solutions.

thanx anyway Michael

heze
Posting Yak Master

192 Posts

Posted - 2006-04-09 : 01:37:57

thanx tan!!!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-09 : 01:44:02

quote:
Originally posted by heze

The script Im writting requires that no human intervention be done, so you mean, if the script executes one thousand times, I have to stop everything, and check if the table exists one thousand times? I think I would not be able to afford the rent if I provided such solutions.

thanx anyway Michael

OK, so your question wasn't how to drop the table, but how to check if it exists:

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

CODO ERGO SUM

heze
Posting Yak Master

192 Posts

Posted - 2006-04-09 : 01:49:19

Appology for lack of precission in my question.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources