Solved

Using temporary tables in Stored Procedures

Posted on 2004-09-22
3
220 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:franksale
3 Comments
 
LVL 9

Accepted Solution

by:
paelo earned 300 total points
ID: 12127248
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
 
LVL 8

Assisted Solution

by:MartinCMS
MartinCMS earned 100 total points
ID: 12127270
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
 
LVL 1

Author Comment

by:franksale
ID: 12127364
Thanks guys it works.  I totally apreciate your help!
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Split data on commas and insert into another table in separate records 26 70
SQL Backup Question 2 29
Powershell v3 - SQLCMD 3 26
Stored Proc - Rewrite 42 56
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

839 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