I stumbled upon a StackOverflow thread this week, where the poster was asking how they could fetch the results of a stored procedure into a temporary table without knowing the resultset’s composition (column layout) in advance. If your first question is “why?”, good for you. If you’re trying to capture the resultset returned by a stored procedure without knowing it’s composition, how in the world can you possibly hope to use said result set?
Whatever the case, the OP clearly wanted to do so. They had gotten as far as concluding that the SELECT…INTO…FROM syntax (which creates the object specified after the INTO keyword) would do the heavy lifting for them, if only they could treat the stored procedure as a table expression (if you have no idea what I’m talking about, check out the examples below). The normal use of the SELECT INTO syntax looks something like this:
SELECT * --Don't use *. You didn't see this here.
INTO #MyTempTable --Drop the hash to create a permanent table.
You can’t substitute a sproc name for your table name, and expect it to work. What you can do is use OPENROWSET or OPENQUERY. Have a look here:
CREATE PROC MyProc
SELECT * FROM sys.databases --Or whatever.
SELECT * FROM #MyTempTable
Neat, eh? Keep in mind that you need to have “Ad Hoc Distributed Queries” enabled:
sp_configure 'Show Advanced Options', 1
sp_configure 'Ad Hoc Distributed Queries', 1
Here’s the original thread – there are a number of very good contributions on the thread.
Thanks to SQL MVP Rob Farley for putting a name to what the OP was asking for – the title of this article was a direct “yoink” from his comment on my post. I would have called it “How to bring back the results of a stored procedure into a temporary table even though you don’t know what the results look like at design time”. I ran short of breath just writing that one.
See you next time.