?
Solved

drop temp table

Posted on 2014-12-02
7
Medium Priority
?
212 Views
Last Modified: 2014-12-02
I have this .... and it is returning null for the temp table even when it has been created.  Why?

 select OBJECT_ID('#TempTable')

IF OBJECT_ID('#TempTable') IS NOT NULL

Drop Table #TempTable

Create Table #TempTable

(
  Name Varchar(max)
  , Age Int
  )


INSERT INTO #TempTable  Values('ddd' , 1 )

Select * From #TempTable
0
Comment
Question by:vbnetcoder
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 20

Accepted Solution

by:
dsacker earned 2000 total points
ID: 40477131
Change your IF statement to:

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

If you're simply wanting to see the object_id of that table, you'll also need to change that top SELECT statement to:

select OBJECT_ID('tempdb..#TempTable')
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40477147
Assuming you just want to drop/recreate the table, and aren't concerned about the OBJECT_ID, give this a whirl..
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
   DROP TABLE #TempTable

CREATE TABLE #TempTable (blah, blah, blah...) 

Open in new window


<edit:  Somewhat redundant with the above post>
0
 

Author Comment

by:vbnetcoder
ID: 40477165
I did the same thing  but with the adventure works 2012 table and it give me a null even when there is a table


IF OBJECT_ID('AdventureWorks2012..#TempTable') IS NOT NULL

Drop Table #TempTable

Create Table #TempTable

(
  Name Varchar(max)
  , Age Int
  )
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 20

Expert Comment

by:dsacker
ID: 40477175
Do not put:

IF OBJECT_ID('AdventureWorks2012..#TempTable') IS NOT NULL

#TempTable names are ALWAYS created in tempdb, no matter where you query them from (i.e., even from AdventureWorks2012).

Only use:

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

The very nature of a # in the #TempTable creates it in tempdb. Every SQL Server has a tempdb, used for temp tables, temp result sets, i.e., the majority of temporary operations or storage means within SQL Server.
0
 

Author Closing Comment

by:vbnetcoder
ID: 40477183
ty
0
 
LVL 20

Expert Comment

by:dsacker
ID: 40477196
Very welcome. Glad that helped.

You may also be interested in DECLARE tables. In a sense, they are even more temp than #temp tables, because they are memory tables (mostly). You create those as follows:

DECLARE @TempTable TABLE
(
  Name Varchar(max)
  , Age Int
  )

INSERT INTO @TempTable  Values('ddd' , 1 )
SELECT * FROM @TempTable
Nice thing about those: They completely disappear after you finish executing the script (or proc). Never a worry about whether they existed before. You can't even code an "IF OBJECT_ID('tempdb..@TempTable') IS NOT NULL" statement and it make sense to SQL Server.
0
 

Author Comment

by:vbnetcoder
ID: 40477204
thanks
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question