Jason Nadal

Restless C#ding

Check for the existence of a sql temp table

Here's an easy way to check if a temp table exists, before trying to create it (ie. for reusable scripts) from Simon Sabin's post :

IF object_id('tempdb..#MyTempTable') IS NOT NULL
BEGIN
   DROP TABLE #MyTempTable
END

CREATE TABLE #MyTempTable
(
   ID int IDENTITY(1,1),
   SomeValue varchar(100)
)
GO


That way, if you have to change databases in the query window, you don't have to drop the tables before you run it again.

Posted: Feb 24 2006, 08:10 AM by thejay2 | with 26 comment(s)
Filed under:

Comments

Bilal Haidar [MVP] said:

Hello Jason:
Although Temp tables might come handy sometimes. However, I knew recently that using a table declared as a TABLE Type is much better, especially that you don't need to make sure the temp table is deleted or not, since it will be automatically deleted, here is an example:

DECLARE @TempTable TABLE
(
Id INT,
Name VARCHAR(50)
)

Then you can deal with the table @TempTable similar to any other table, with some limitations, but at least it does what is required from a temp table.

Hope that helps,
Regards
# February 24, 2006 8:24 AM

Bill said:

Sometimes a temp table is necessary.  For example, if you are inserting data from executing a sproc.

Example:

INSERT INTO #job_status

EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,'sa', @jobid

# July 12, 2007 12:16 PM

zom said:

Declared table variables suffer a performance hit when dealing with larger (1k records+) datasets in my experience.

Temp tables are still king when dealing with temporary data sets for performance.

# April 16, 2008 11:02 AM

Smita said:

Hey, it works gr8!!

Thanks!!

Cheers!!

# April 16, 2008 5:23 PM

Gnana said:

that was useful! thanks!

# April 17, 2008 10:14 AM

Ray said:

I like in memory tables too. If you need to use it in a loop with an identity column where you want to repopulate the in memory table each time thru the loop. Truncate table will not work, and Delete table, doesn't reset the identity. I guess you can calculate it.

# April 17, 2008 4:55 PM

dev said:

thanks dude it works i was finding a lot in server 2000 books.

but i found over here.

again thanks for the help

# July 31, 2008 3:13 AM

Daft said:

Cool tip, works a treat, thanks :)

# August 7, 2008 7:04 AM

moongy said:

oh, thats what i looking for. thanks alot !

# September 16, 2008 5:13 AM

HuyNVT said:

Wow, thats what i'm looking for too. Thanks in advance

# October 25, 2008 5:26 AM

hannah said:

thanks!  that solves my problem!!!  

# November 6, 2008 11:12 AM

Anthony A. said:

Exactly what i was looking for. tyvm.

# February 3, 2009 5:52 PM

Kevin said:

Thanks Bilal Haidar, thats a great solution!

# February 17, 2009 7:39 AM

Quentin said:

Thanks for the post :)

Just a note on the EXEC into # - when using EXEC INSERT it can mess up your transaction if you are using one.

# March 13, 2009 5:07 AM

Charles said:

Thanks Jason.  That did the trick!

# April 14, 2009 12:23 PM

Andy said:

Thanks for the tip - and by the way, although I usually use in memory table variables, there are occasions when temporary tables do perform better, especially with large queries.

# June 5, 2009 10:04 AM

E Knapp said:

Appreciate finding this quick post on checking for temp table existence. And yes the temp table approach is necessary for  handling large amounts of data, in my case > 100,000 rows. The table variable would not perform doing cross table joins, etc.

# June 16, 2009 11:26 AM

Bob said:

Thanks, After I used temp table, the running time of my query change from 5 minutes to 10 seconds.

# July 9, 2009 4:55 PM

Venkat said:

It was really a great help ..

thanks dude..

cheers!

Venkat

# July 15, 2009 9:26 PM

Ryan said:

different approach ... was userful for me..

# August 13, 2009 4:38 PM

Saša said:

Helped me a lot, thanks

# September 22, 2009 7:26 AM

Marion said:

ditto!!! works great!!!

# January 7, 2010 3:49 PM

CarverDown said:

Don't stop posting such themes. I like to read stories like this. Just add some pics :)

# January 23, 2010 12:45 AM

AJ said:

It did the trick for me as well...

Cool post with the exact solution one might be looking for...

# January 29, 2010 4:12 AM

Nick said:

So why when I create this table . . .

CREATE TABLE #MyTempTable

(

  ID int IDENTITY(1,1),

  SomeValue varchar(100)

)

GO

does this code . .

select object_id('#MyTempTable')

return NULL ?

I'm on SQL 2008 R2

# March 7, 2011 5:56 AM

yany1 said:

Try:

SELECT object_id('tempdb..#MyTempTable')

# April 11, 2011 11:32 AM