Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Drop Table if exists 2

Status
Not open for further replies.

jgurgen

Programmer
Mar 9, 2006
192
US
In the beginning of my proc i want to drop the temp table if it exists. in mysql it would be...
DROP TABLE IF EXISTS Temp_Table

how do i do it in MSSQL?
 
What kind of temp table is it? Is it static or a #temp table?

- Paul
10qkyfp.gif

- If at first you don't succeed, find out if the loser gets anything.
 
right now i have it as
CREATE TABLE TEMP_TABLE
 
That isn't a temp table for one. SQL Server has the same related functional If Exists keyword and functionality




[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]
 
Use this to drop a static table.
Code:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEMP_TABLE]') 
and OBJECTPROPERTY(id, N'IsTable') = 1)
DROP TABLE [dbo].TEMP_TABLE
GO

But you may want to use SQL Server temp tables. Look up temp tables in BOL.



- Paul
10qkyfp.gif

- If at first you don't succeed, find out if the loser gets anything.
 
would i be better in doing

CREATE TABLE #Temp_Table

then would i have to check if the table exists in case the procedure crashed?
 
Inthe first place you do not want to create a temp table that way, it will create an actual table in your database.
This is a very inefficent way to do business and you can run into problems if multiple people are accessing the data.

YOu want to create a true temp table or a table variable.

CREATE TABLE #TEMP_TABLE

This will create a table in teh temp db that will go out of scope and drop as soon as the proc is done running. No one other than the user running the proc can see this table and two users can run the same proc simulataneously with different input values and each get their own table.

In this case you generally do not need to check for existence because if you haven't created it yet in the proc, it doesn't exist.

To check for existence of a table
Code:
IF  EXISTS (SELECT * FROM test..sysobjects WHERE type = 'U' AND name = 'Mytable')
Begin
insert some code
End
[\code}





Questions about posting. See faq183-874
 
If you use # is will be destroyed upon that sessions ending. If you use ## then it is global and it would be best to check for it.

Take Paul's suggestion on looking in BOL for temp tables.

I would not create static tables to represent "temp" tables. It is not a good idea

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top