?
Solved

Stored procedure results into undefined temporary table

Posted on 2008-11-18
5
Medium Priority
?
786 Views
Last Modified: 2012-05-05
Hello experts,

I'd like to be able to insert the results of a stored procedure into a temporary table without previously defining the fields for a temporary table.  I was hoping the following would work:

SELECT * INTO #temp
EXEC mySP

or

INSERT INTO #temp
EXEC mySP

But I haven't had success with either.

Thanks for any help!
0
Comment
Question by:iqmonkey
4 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22983888
only one way to do it using an openrowset query
an alternative is inserting the records in table variable
0
 
LVL 7

Expert Comment

by:thiyaguk
ID: 22983905
Try using Global Temporary Variables

##temp
0
 

Author Comment

by:iqmonkey
ID: 22984411
Some more detail - I should have said that the stored procedure has session specific input parameters, as it is used in reporting, so global temp table isn't going to work for this.

It is also likely to return >1k records, so I' don't really want to use a table variable.

Just trying openrowset -  I've got a few issues with building the string, setting up surface area and whatever - it is fiddly, and now I'm leaning towards just defining the damn table!

I'll leave the question open for a little bit, but thanks for your help so far, all of your answers are valid.
0
 

Accepted Solution

by:
ee_auto earned 0 total points
ID: 23288996
Question PAQ'd, 500 points not refunded, and stored in the solution database.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

864 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