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!
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!
Try using Global Temporary Variables
##temp
##temp
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
an alternative is inserting the records in table variable