Stored procedure results into undefined temporary table

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!
iqmonkeyAsked:
Who is Participating?
 
ee_autoConnect With a Mentor Commented:
Question PAQ'd, 500 points not refunded, and stored in the solution database.
0
 
momi_sabagCommented:
only one way to do it using an openrowset query
an alternative is inserting the records in table variable
0
 
thiyagukTech Lead Commented:
Try using Global Temporary Variables

##temp
0
 
iqmonkeyAuthor Commented:
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
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.