Link to home
Start Free TrialLog in
Avatar of iqmonkey
iqmonkey

asked on

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!
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

only one way to do it using an openrowset query
an alternative is inserting the records in table variable
Try using Global Temporary Variables

##temp
Avatar of iqmonkey
iqmonkey

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of ee_auto
ee_auto

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial