{"id":1272,"date":"2015-09-09T15:20:59","date_gmt":"2015-09-09T05:20:59","guid":{"rendered":"http:\/\/ntsblog.homedev.com.au\/?p=1272"},"modified":"2024-06-08T20:32:35","modified_gmt":"2024-06-08T10:32:35","slug":"t-sql-drop-temp-table-exists","status":"publish","type":"post","link":"https:\/\/ntsblog.homedev.com.au\/index.php\/2015\/09\/09\/t-sql-drop-temp-table-exists\/","title":{"rendered":"T-SQL Drop temp table if it exists"},"content":{"rendered":"<div id=\"ntsbl-2449015692\" class=\"ntsbl-before-content ntsbl-entity-placement\"><script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-6288941070289539\" crossorigin=\"anonymous\"><\/script><ins class=\"adsbygoogle\" style=\"display:inline-block;width:728px;height:90px;\" \ndata-ad-client=\"ca-pub-6288941070289539\" \ndata-ad-slot=\"9356781486\"><\/ins> \n<script> \n(adsbygoogle = window.adsbygoogle || []).push({}); \n<\/script>\n<\/div>\n<h2 class=\"wp-block-heading\">The Problem<\/h2>\n\n\n\n<p>I often work in SQL Server with temp tables and they can be a pain when developing as you may have code like<br><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql line-numbers\">Create Table #tblTemp( Id int , Value nvarchar(32))\n\nInsert Into #tblTemp\nselect Id, Value from SomeOtherTableAndLotsOfJoins\n\n--Drop Table #tblTemp<\/code><\/pre>\n\n\n\n<p>You comment out the drop as you want to check the results and do some testing but next time you want to run the script you have to remember to call the &#8220;Drop Table&#8221;<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">The Solution<\/h2>\n\n\n\n<p>Put this at the top of your script<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql line-numbers\">if exists(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#tblTemp'))\nbegin\n    drop table #tblTemp;\nend<\/code><\/pre>\n\n\n\n<p>As of SQL 2016 or later<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql line-numbers\">DROP TABLE IF EXISTS #tlbTemp;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>The Problem I often work in SQL Server with temp tables and they can be a pain when developing as you may have code like [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[28],"tags":[],"class_list":["post-1272","post","type-post","status-publish","format-standard","hentry","category-t-sql"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/1272","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/comments?post=1272"}],"version-history":[{"count":0,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/1272\/revisions"}],"wp:attachment":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/media?parent=1272"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/categories?post=1272"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/tags?post=1272"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}