Dropping a temp Table

Dropping a temp Table

Post by JonesG » Mon, 16 Jun 2003 18:56:07



I use the following syntax to drop a temp table just incase it exists
already (It shouldnt but ...)

if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[#tpR1100_Table_NationalOUCtemp]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[#tpR1100_Table_NationalOUCtemp]

Should this work?

 
 
 

Dropping a temp Table

Post by David Porta » Mon, 16 Jun 2003 19:40:45


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

--
David Portas
------------
Please reply only to the newsgroup
--


Quote:> I use the following syntax to drop a temp table just incase it exists
> already (It shouldnt but ...)

> if exists (select * from dbo.sysobjects
> where id = object_id(N'[dbo].[#tpR1100_Table_NationalOUCtemp]') and
> OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[#tpR1100_Table_NationalOUCtemp]

> Should this work?


 
 
 

Dropping a temp Table

Post by JonesG » Mon, 16 Jun 2003 20:52:19


Thanks David,

I think thats resolved it.

GJ


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

> --
> David Portas
> ------------
> Please reply only to the newsgroup
> --



> > I use the following syntax to drop a temp table just incase it exists
> > already (It shouldnt but ...)

> > if exists (select * from dbo.sysobjects
> > where id = object_id(N'[dbo].[#tpR1100_Table_NationalOUCtemp]') and
> > OBJECTPROPERTY(id, N'IsUserTable') = 1)
> > drop table [dbo].[#tpR1100_Table_NationalOUCtemp]

> > Should this work?

 
 
 

Dropping a temp Table

Post by Raj Bab » Tue, 17 Jun 2003 00:48:50


Am using a series of temp tables in a procedure.

Is it good programming to drop the temp tables once am done with it (i
mean half way in the procedure I know am not going to call a particular
temp table again)in the procedure or can we just leave to die out once
the session is over.

Do we care about the growth of the temdb?

-- Raj

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Dropping a temp Table

Post by Tibor Karasz » Tue, 17 Jun 2003 13:47:37


I don't mind having these be cleaned up by the system. Unless I have a big temp table and expect
to create another big one (you might want to avoid having these two at the same time).

You need the space in tempdb that you need. To reduce the amount of space needed, you could
check indexes, queries, query plans etc. Also, you can specify that tempdb is created with your
desired size at startup (ALTER DATABASE).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...


Quote:> Am using a series of temp tables in a procedure.

> Is it good programming to drop the temp tables once am done with it (i
> mean half way in the procedure I know am not going to call a particular
> temp table again)in the procedure or can we just leave to die out once
> the session is over.

> Do we care about the growth of the temdb?

> -- Raj

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

1. Simple question: cannot create, drop, recreate temp table

I came across a scenario today where I needed to create a temp table
twice, with slightly different columns in the 2nd table. Logically
however, the name "Sums" described each table.

But this doesn't work:

===================================================================

create table #sums (N int primary key)
-- do stuff with sums...
drop table #sums

create table #sums (N int primary key)
-- do other stuff with sums...
drop table #sums

Server: Msg 2714, Level 16, State 1, Line 6
There is already an object named '#sums' in the database.

===================================================================

Obviously I solved this by creating two different tables, called
"SumsByX" and "SumsByY", but I wondered why I couldn't do what I
wanted.

--
The Fool

2. SQL Anywhere - some evaluation questions

3. drop all temp tables in QA session

4. Another Tx enlist error

5. Problem Dropping local temp tables

6. Deleted BULTIN\Administrators

7. no drop of temp table effects?

8. DATA LOSS RANDOMLY - URGENT PLS !!!

9. how to drop a temp table

10. When to drop a temp table

11. dropping a #temp table

12. How to drop global temp table before recreate it?

13. Deadlock from dropping a temp table in a stored proc?