how to drop a temporary 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)

how to drop a temporary table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-10 : 19:28:05

Katarina writes "How do you check if a temporary table exists before you drop it?
I was trying with the statement below but it doesn't work because
object_id('#temp') is null, it only works with permanent tables.
IF OBJECTPROPERTY ( object_id('#temp'),'ISTABLE') = 1 DROP TABLE #temp"

Nazim
A custom title

1408 Posts

Posted - 2002-02-11 : 01:44:14

How about this
if Exists (select * from tempdb..information_Schema.tables where substring(table_name,1,len('#tt')) like '#tt')
drop table #tt

Note: substring(table_name,1,len('#tt')) is used instead of simply using table_name coz Sql Server has a different way of naming temp tables for internal usage. it suffixes temptables with series of Underscores(__ ) and at end end a numeric value is added.

Eg:#tt_________________________________________________________________________________________________________________000000000009

HTH

--------------------------------------------------------------
"Happiness is not something you experience, it's something you remember."

Edited by - Nazim on 02/12/2002 08:01:59

Nazim
A custom title

1408 Posts

Posted - 2002-02-12 : 03:56:07

This should also work out if you point your temp table to access it from tempdb. Always remember the temporary tables are created in tempdb irrespective of where in which database was in use when the temp table was created.

IF OBJECTPROPERTY ( object_id('tempdb..#temp'),'ISTABLE') = 1 DROP TABLE #temp"

HTH

--------------------------------------------------------------
"Happiness is not something you experience, it's something you remember."

Edited by - Nazim on 02/12/2002 08:01:16

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-02-12 : 07:40:51

Hi

I don't think that will necesarily work. If another user has a temp table called #tt then you may drop theirs instead.

Not that I know the answer

.. I have been waiting for someone that is a better DBA than me (not very hard) to come up with another idea.

Damian

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-12 : 07:55:43

I've tried this numerous times:

DROP TABLE #temp
SELECT * INTO #temp FROM pubs..authors

It throws an error if the temp table doesn't exist, but it doesn't stop execution. Unless your code throws up on any kind of error message, you should be able to get by with this.

Nazim
A custom title

1408 Posts

Posted - 2002-02-12 : 08:00:36

Aha! How about specifying your ownername too.

IF OBJECTPROPERTY ( object_id('tempdb.dbo.#temp'),'ISTABLE') = 1 DROP TABLE #temp"

HTH

--------------------------------------------------------------
"Happiness is not something you experience, it's something you remember."

Edited by - Nazim on 02/12/2002 08:03:10

ttcrissy
Starting Member

8 Posts

Posted - 2002-02-12 : 08:41:31

Why not try this?

if exists (select * from [tempdb]..sysobjects where id = OBJECT_ID(N'tempdb..#tmpTable'))
drop table #tmpTable

I use this all the time and it works!!!

Best luck!!!

nricardo
Starting Member

17 Posts

Posted - 2002-02-12 : 12:25:48

quote:
I don't think that will necesarily work. If another user has a temp table called #tt then you may drop theirs instead.

AFAIK, only the temp table in the current session with that name will be dropped. You can have different sessions with the same table name and SQL Server treats tables from other sessions as non-existent.

CalvinLee
Starting Member

2 Posts

Posted - 2002-02-14 : 14:08:17

Perhaps you can try the new table variable ( SQL 2000 ) instead of using a temporary table.

Sample:
DECLARE @tablename table ( column_id int, column_string varchar(50) )

Katarina
Starting Member

6 Posts

Posted - 2002-02-21 : 09:54:23

quote:

Why not try this?

if exists (select * from [tempdb]..sysobjects where id = OBJECT_ID(N'tempdb..#tmpTable'))
drop table #tmpTable

I use this all the time and it works!!!

Best luck!!!

Many thanks!! This is probably the most effective and simplest solution.

Katarina
Starting Member

6 Posts

Posted - 2002-02-21 : 09:57:44

quote:

Perhaps you can try the new table variable ( SQL 2000 ) instead of using a temporary table.

Sample:
DECLARE @tablename table ( column_id int, column_string varchar(50) )

I'm using SQL 7 server. Is this table variable available only in SQL 2000?

Katarina
Starting Member

6 Posts

Posted - 2002-02-21 : 09:59:41

quote:

I've tried this numerous times:

DROP TABLE #temp
SELECT * INTO #temp FROM pubs..authors

It throws an error if the temp table doesn't exist, but it doesn't stop execution. Unless your code throws up on any kind of error message, you should be able to get by with this.

I also use this in queries, but this won't work if your code is in
a stored procedure, they don't like any error messages!

Katarina
Starting Member

6 Posts

Posted - 2002-02-21 : 10:05:30

quote:

This should also work out if you point your temp table to access it from tempdb. Always remember the temporary tables are created in tempdb irrespective of where in which database was in use when the temp table was created.

IF OBJECTPROPERTY ( object_id('tempdb..#temp'),'ISTABLE') = 1 DROP TABLE #temp"

HTH

--------------------------------------------------------------
"Happiness is not something you experience, it's something you remember."

Edited by - Nazim on 02/12/2002 08:01:16

Nazim, I don't understand how will this work, because
select OBJECTPROPERTY ( object_id('tempdb..#temp'),'ISTABLE')
returns null, not 1!!

About your first solution:

if Exists (select * from tempdb..information_Schema.tables where substring(table_name,1,len('#tt')) like '#tt')
drop table #tt

it gave me an error that 'tempdb' is not a linked server and that
I should execute sp_addlinkedserver. I'm not sure what is that all about.(?)
Thank you for your time!

izaltsman
A custom title

1139 Posts

Posted - 2002-02-21 : 10:22:19

quote:

Nazim, I don't understand how will this work, because
select OBJECTPROPERTY ( object_id('tempdb..#temp'),'ISTABLE')
returns null, not 1!!

I think it depends on which version of SQL Server you have installed... If you were on SQL2k, you'd get 1, but on SQL7 you get NULL

quote:

if Exists (select * from tempdb..information_Schema.tables where

There is an extra period in there. The view should be referenced as:

tempdb.information_schema.tables

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-21 : 10:49:58

You should know whether or not the temp table has been created so shouldn't have to check. If not you shuold probably restructure the code.
Given that
object_id('tempdb..#xxx') should work.
also temp tables are created with an extension that is unique to the spid.
You can create a temp table with the spid in the name, look at sysobjects for the table to get the extension then query sysobjects for the table you want with the correct extension fo rthe spid.

Note that none of these methods are guaranteed to work for any future version.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

CalvinLee
Starting Member

2 Posts

Posted - 2002-02-22 : 10:36:45

quote:

I'm using SQL 7 server. Is this table variable available only in SQL 2000?

It is a new feature in SQL 2000. Doesn't work in SQL 7.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources