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 62 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

Michelle said:

Thanks you honey, just what I needed.. ;-)

# June 17, 2009 8:47 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

StephanJade said:

Great post you got here. I'd like to read more concerning that topic.

# October 25, 2009 9:03 AM

saqib said:

nice post.this help me out .Thanks alot

# December 1, 2009 3:00 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

Ori said:

Hi guys. The function of science fiction is not always to predict the future but sometimes to prevent it. Help me! Can not find sites on the: Proventil hfa side effects. I found only this - <a href="genericproventil.info/.../">proventil rebate</a>. There are loud electronic expectation behaviors right for calendar, proventil. Proventil, and interact if about we have one of the canby hall witnesses, to assume. Thanks for the help :-(, Ori from Monaco.

# March 25, 2010 1:08 PM

Abi CLARK said:

Indeed great article you have here. It would be great to read a bit more about that topic. Thanx for posting that data.

Abi CLARK

<a href=" www.renttobuyguide.co.uk/">rent to buy</a>

# April 26, 2010 2:20 PM

Evie Collins said:

Really great article u have here. I'd like to read something more concerning this theme. Thanks for posting that information.

Evie Collins

<a href="www.waybiz.com/">Find products</a>

# May 3, 2010 7:40 PM

Jim said:

Thanks for the quick and useful tip!

# June 22, 2010 1:31 PM

PasserBy said:

Great article you got here. It would be great to read more concerning this topic. The only thing your blog misses is a few pictures of some gadgets.

Nickolas Stepman

<a href="www.jammer-store.com/">10m cell phone jammers</a>

# July 7, 2010 5:10 PM

London escort female said:

It was rather interesting for me to read the post. Thanks for it. I like such topics and anything connected to them. I definitely want to read more on that site soon. BTW, rather good design you have at this site, but don’t you think design should be changed every few months?

Natasha Smith

# July 12, 2010 6:25 AM

east indian escort said:

It is certainly interesting for me to read this blog. Thanks for it. I like such themes and anything connected to them. I definitely want to read more on this site soon.

Alex Meetington

# July 17, 2010 10:28 PM

female escort services said:

Pretty good blog to pay attention to at least for me. A small question, why don't you add this article to social media? This may bring much traffic here.

# July 22, 2010 12:35 PM

David Smith said:

It was certainly interesting for me to read this post. Thanx for it. I like such themes and everything connected to this matter. I would like to read a bit more soon. BTW, rather good design you have here, but how about changing it every few months?

David Smith

<a href="http://www.baccaratgirls.com">high class escort service</a>

# August 2, 2010 5:56 PM

Steve said:

Great tip, thanks :)

# November 22, 2010 5:20 PM

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

Kev proventil | EsIsolutions said:

Pingback from  Kev proventil | EsIsolutions

# March 24, 2011 3:48 PM

yany1 said:

Try:

SELECT object_id('tempdb..#MyTempTable')

# April 11, 2011 11:32 AM

quisteasseree said:

I’ve been visiting your blog for a while now and I always find a gem in your new posts.  Thanks for sharing.

# June 1, 2011 3:14 PM

grinder said:

i like big butts and i cannot lie

# August 12, 2011 4:54 AM

hooher tod said:

Yes there should realize the reader to RSS my feed to RSS commentary, quite simply

# September 14, 2011 7:17 AM

Buy OEM software online said:

3lOE1W Comrade kill yourself.

# September 24, 2011 8:06 AM

Cheap oem software said:

WTHJVy Well, actually, a lot of what you write is not quite true !... well, okay, it does not matter:DD

# November 5, 2011 10:43 PM

Suppliers said:

h8eAhi Hey, thanks for the article.Much thanks again.

# April 6, 2012 12:03 AM

l Backlinks w said:

I'm not sure why but this site is loading very slow for me. Is anyone else having this problem or is it a problem on my end? I'll check back later on and see if the problem still exists.

# September 3, 2012 10:21 AM

icon clip art said:

 I apologise, but I suggest to go another by.

P.S. Please review <a href="iconadiprua.deviantart.com/.../Medical-Tab-Bar-Icons-283566246">Medical Tab Bar Icons from Iconadiprua</a>

# September 22, 2012 4:07 AM

icon download said:

 I think, that you are mistaken. Let's discuss. Write to me in PM.

<a href="www.hpixel.com/.../a>

# September 23, 2012 1:14 AM

icons downloads said:

 Things are going swimmingly.

<a href="www.hpixel.com/.../a>

# September 24, 2012 8:01 AM

icon design said:

<a href="pcwin.com/.../screen.htm"> I am sorry, that I interfere, but, in my opinion, this theme is not so actual.</a>

# October 9, 2012 2:51 PM

icons set said:

<a href="www.myzips.com/.../Ribbon-Bar-Icon-Set.phtml"> I am am excited too with this question.</a>

# October 10, 2012 2:55 AM

cheap seo services said:

CWkdqc Fantastic article post. Will read on...

# October 21, 2012 12:03 AM

icon downloads said:

[url=www.mahdinaforum.com/.../viewtopic.php] I thank for the information, now I will not commit such error.[/url]

# November 4, 2012 11:15 AM

ngszwf@gmail.com said:

Greetings from Carolina! I'm bored at work so I decided to check out your blog on my iphone during lunch break. I enjoy the knowledge you present here and can't wait to take a look when I get home. I'm surprised at how quick your blog loaded on my mobile .. I'm not even using WIFI, just 3G .. Anyhow, amazing site!

# November 7, 2012 3:29 AM

xshzesiv@gmail.com said:

Amazing! This blog looks just like my old one! It's on a totally different topic but it has pretty much the same layout and design. Outstanding choice of colors!

# November 8, 2012 1:43 AM

Social Bookmarking Service said:

W0TLoZ Looking forward to reading more. Great article.Thanks Again. Great.

# November 26, 2012 11:16 PM

tfgzls@gmail.com said:

My developer is trying to convince me to move to .net from PHP. I have always disliked the idea because of the costs. But he's tryiong none the less. I've been using WordPress on a number of websites for about a year and am anxious about switching to another platform. I have heard very good things about blogengine.net. Is there a way I can transfer all my wordpress content into it? Any help would be really appreciated!

# December 25, 2012 8:34 PM

social bookmarking seo said:

Say, you got a nice article.Much thanks again. Really Cool.

# January 16, 2013 10:52 PM

Sierra said:

Hey There. I discovered your weblog the use of msn.

That is a really neatly written article. I'll make sure to bookmark it and return to read more of your helpful information. Thanks for the post. I will definitely return.

# February 1, 2013 12:09 PM

pills for lose weight said:

7o1l9x Thanks a lot for the blog.Thanks Again. Really Cool.

# February 1, 2013 7:59 PM

Social bookmarks said:

4a8wIS Im obliged for the blog article.Much thanks again. Really Great.

# March 20, 2013 8:08 PM
Leave a Comment

(required) 

(required) 

(optional)

(required)