• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

drop temp table

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
vbnetcoder
Asked:
vbnetcoder
  • 3
  • 3
1 Solution
 
dsackerContract ERP Admin/ConsultantCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
vbnetcoderAuthor Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
dsackerContract ERP Admin/ConsultantCommented:
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
 
vbnetcoderAuthor Commented:
ty
0
 
dsackerContract ERP Admin/ConsultantCommented:
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
 
vbnetcoderAuthor Commented:
thanks
0

Featured Post

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!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now