Need to create & select #temp data table

Question

Need to create & select #temp data table

Express12

SSCoach

Points: 18526
More actions
June 21, 2007 at 2:04 pm

#172960

The following script is NOT allowing me to SELECT * FROM #temp_Users -- (last statement below)
Any idea how I can dynamically create a #temp table then select from it? (In this scenario we prefer NOT to create a permanent table in the DB and subsequently drop it)
IF object_id('#temp_Users') is not null
DROP TABLE [#temp_Users]
GO
DECLARE @sql VARCHAR(1000)
SET @sql = 'select * INTO #temp_Users FROM MyDatabase..Users '
EXEC(@SQL)
--select * INTO #temp_Users FROM MyDatabase..Users
SELECT * FROM #temp_Users
BT

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

The table is created in the exec statement. It is automatically destroyed when that connection is dropped (before the execution goes back to the procedure).

You'll have to create that table before the EXEC SQL ('select * INTO #temp_Users FROM MyDatabase..Users WHERE 1 = 0)

Then in the exec : INSERT INTO #temp_Users (Columns) SELECT ...

Then you'll have access to the data in the master procedure.

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 2

Ninja's right... but going beyond that there are some other issues here:

When you check the existence of a temporary table using object_id(), you have to reference it in tempdb... so change your IF statement to: IF object_id('tempdb..#temp_users') is not null...

There's also no reason to use dynamic sql in this example. Unless you're simplifying it for posting, just use his example without the EXEC()... In my experience Dynamic sql can be a useful tool but is kind of a nasty headache a lot of the time when it's not necessary.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Ya I'm kind of sick of that don't use dynamic sql discussion. If they want to hang themselves, I let 'em. I'm not here to save the world anymore. But your point, that point is still very valid.

I was assuming here that the code was something around a dynamic where clause. That's also why I didn't mention it. But if the presented code is the actual code, there's absolutely no need for dynamic sql. There's even no need for the temp table... Need I go on?

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 4

I agree with Aaron and Remi... there is no need for any dynamic SQL here. Also, you must follow Aaron's suggestion for how to test for the presence of a temp table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

The best way to check for a temp table (SQL 2005 only) is to also include the object type parameter in the OBJECT_ID function call. This makes sure that it is a table, and not some other type of temporary object.

if object_id('tempdb..#temp_users','u') is not null
print 'table #temp_users exists'
else
print 'table #temp_users does not exist'

For SQL 2000 and before, you can do it this way:

if exists (
 select  *
 from
  tempdb.dbo.sysobjects o
 where
  o.xtype in ('U') and
  o.id = object_id( N'tempdb..#temp_users' )
 )
 print '#temp_users exists'
else
 print '#temp_users does not exist'

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Thanks for the tip, this may avoid some small gotchas that can make you go mad. The object is a very nice touch on this one.