Dropping a temp table - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Dropping a temp table

Author

Topic

jaford
Starting Member

14 Posts

Posted - 2004-03-03 : 15:40:39

Can someone give me the correct syntax for checking the existance of a temp table and then dropping it if it does exist? THANKS!

Something like:

if exists [#temptable] drop table [#temptable]

shafiqm
Starting Member

9 Posts

Posted - 2004-03-03 : 15:42:17

-- Drop the copy of ItemMast if exists
if exists (select * from sysobjects where name = 'ItemMastx')
begin
drop table ItemMastx
end

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-03 : 15:47:44

[code]

CREATE TABLE #myTemp99(Col1 int)

IF EXISTS (SELECT COUNT(*) FROM #myTemp99)
BEGIN
DROP TABLE #myTemp99
END

[/code]

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-03 : 15:50:33

quote:
Originally posted by shafiqm

-- Drop the copy of ItemMast if exists
if exists (select * from sysobjects where name = 'ItemMastx')
begin
drop table ItemMastx
end

ah, but that's not a temp table...cut and paste this in to QA



CREATE TABLE #myTemp99(Col1 int)

SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#%'

IF EXISTS (SELECT COUNT(*) FROM #myTemp99)
  BEGIN
	DROP TABLE #myTemp99
  END

EDIT: You know what, I betcha SLQ Server optimized the SELECT * for this situation. Use that instead of the COUNT

Brett

8-)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-03-03 : 22:42:36

There's also:

IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL DROP TABLE #tempTable

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-04 : 15:30:25

Are there any good reasons for using a temp-table over a table-variable...? I just can't seem to find any, I know that they are possible to use over different procedures in the same scope but I have just never had the use for that.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

nfsoft
Starting Member

36 Posts

Posted - 2004-03-04 : 19:52:33

I use ...
if exists(select * from tempdb..sysobject where name like 'myTemp99%')
drop table #myTemp99

be aware that #myTemp9999_some_other_table will olso be afected and droped.

Nuno Ferreira

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-04 : 19:56:22

quote:
Originally posted by Lumbago

Are there any good reasons for using a temp-table over a table-variable...?

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources