Using temporary tables in Stored Procedures

Hello everyone that can help me,

i am trying to create a stored procedure that uses a temporary table.  I want to be able to Use a Select Into statement to populate information into a tempory table.  

Then I want to run another Select Into statement to populate the same tempory table.  Then view the results of that tempory table.  It seems easy enough, through all of the books that I have on the subject, I am somewhat confused how to make it work in real life.

So far this is what I have:

Create Procedure dbo.usp_test AS
Select Account
Into #temptable
From Example_Table1
Go
Select Account
Into #temptable
From Example_Table2
Go
Select * from #temptable

Obviously it doesn't work.  As you can probably see, my experience with Procedures is limited. Can anyone help me with this? please help.
LVL 1
franksaleAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
paeloConnect With a Mentor Commented:
I don't think you can perform subsequent SELECT INTO operations for the same temp table name.  You could try SELECT followed by INSERT:

Select Account
Into #temptable
From Example_Table1
Go
Insert Into #temptable
Select Account
From Example_Table2
Go


But you are probably better off explicity declaring your temporary table:


CREATE TABLE #temptable (
 Account varchar(20) --or whatever
)

Insert Into #temptable
Select Account
From Example_Table1
GO
Insert Into #temptable
Select Account
From Example_Table2
GO

DROP TABLE #temptable --not really necessary, local temps are dropped at the end of the proc


-Paul.
0
 
MartinCMSConnect With a Mentor Commented:
Create Procedure dbo.usp_test AS

Insert into temptable    (Account)
Select Account From Example_Table1

Insert into temptable    (Account)
Select Account From Example_Table2

Select * from temptable

Go
0
 
franksaleAuthor Commented:
Thanks guys it works.  I totally apreciate your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.