Temp table in Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Temp table in Stored Procedure

Author

Topic

Force
Starting Member

4 Posts

Posted - 2006-11-15 : 16:16:55

I am creating a temp table from a union of two tables in two different db's and then running a query against another table in another db. They are all on the same server. The following code runs like a rocket and returns the correct results when when run in the Query Analyzer:

Select c1,c2
,c3,c4
,c5,c6 Into #MarkTest
From db1.dbo.table1
Where convert(char(12),startDateTime,101) = '11/14/2006'

UNION ALL

Select c1,c2,c3,c4,c5,c6
From db2.dbo.table2
Where convert(char(12),startDateTime,101) = '11/14/2006'
GO

Select *
From #MarkTest c
Where convert(char(12),startDateTime,101) = '11/14/2006'
And NOT EXISTS (Select 'x'
From db3.dbo.c1 b
Where c.c1 = b.c1
And convert(char(12),startDateTime,101) = '11/14/2006')

GO

Drop Table #MarkTest

When I try to create a stored procedure from this query I get the following error:

Invalid Object Name #MarkTest

Is this choking on the validation as when the system parses the query #markTest doesn't exist? How can I write this as a stored procedure?
Thanks for any assistance.

Kristen
Test

22859 Posts

Posted - 2006-11-15 : 16:28:34

The "compiler" can get in a bit of twist. e.g.


CREATE PROCEDURE foo
AS
SELECT *
INTO #TEMP
FROM MyTable
...
DROP TABLE #TEMP

SELECT *
INTO #TEMP
FROM MyOtherTable

Simple answer is to pre-create the #TEMP table at the top of the SProc, rather than using SELECT ... INTO #TEMP FROM

This is also preferred because the SELECT ... INTO route will hold a Lock on Table Creating in TEMDB for the duration of the query, where pre-creating the table will only hold the lock for just the time to Create the table itself, and not be influenced by the time to run the query.

(And if you pre-create the #TEMP table you can:

a) Index/PK it
b) Put COLLATE statements on any char/varchar/text columns - so you are not at the mercy of TEMPDB having a different collation to your database/underlying table

Kristen

Force
Starting Member

4 Posts

Posted - 2006-11-15 : 16:47:53

Kristen,

I really appreciate the assist. What I don't see is how to load the temp table with the Union results without using the "Into #MarkTest.
When you say pre-create the temp table do you mean like this?

Create Table #MarkTest(collectionID varchar(16)
,donornum varchar(16)
,lastname varchar(30)
,firstname varchar(28)
,batch uniqueidentifier(16))

How would I then load the results of the union into the temp table? Again, I really appreciate the help.

Mark

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-15 : 17:01:08

INSERT INTO #MarkTest(Collist) SELECT...

????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-15 : 17:11:07

You need to remove those GO statements when you create the proc and it should work just fine.

Force
Starting Member

4 Posts

Posted - 2006-11-15 : 17:16:07

Insert Into #MarkTest(c1,c2,c3,c4,c5)

Select c1,c2,c3,c4,c5
From db1.dbo.tbl1
Where convert(char(12),startDateTime,101) = '11/14/2006'

UNION ALL

Select c1,c2,c3,c4,c5
From db2.dbo.tbl2 Where convert(char(12),startDateTime,101) = '11/14/2006'
GO

When I try this it gives me the error "Invalid Object name '#MarkTest'
Where do I go from here? Thanks.

Mark

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-15 : 17:18:16

You need to remove those GO statements when you create the proc and it should work just fine.

Force
Starting Member

4 Posts

Posted - 2006-11-15 : 18:21:06

snSQL,

You are right on the money. Awesome. Thanks.

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources