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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
ee_autoCommented:
Question PAQ'd, 500 points not refunded, and stored in the solution database.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.