Solved

drop temp table

Posted on 2014-12-02
7
210 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 500 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

688 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