Doing a SELECT INTO using a Stored Proc as the data source

[Deane]

I have a stored proc that returns a resultset.

I would like to deposit that resultset into a table. Kind of like a
"SELECT INTO", but using a stored proc.

If I could do this --

SELECT INTO MyTable FROM MyStoredProc

-- I'd be happy.

What are the alternatives?

[Tom van Stiphout]

On Sat, 10 May 2008 20:29:14 -0700 (PDT), Deane
<de**********@gmail.comwrote:

Why would you want to store the resultset in a table if you can simply
re-run the sproc?
-Tom.

>I have a stored proc that returns a resultset.

I would like to deposit that resultset into a table. Kind of like a
"SELECT INTO", but using a stored proc.

If I could do this --

SELECT INTO MyTable FROM MyStoredProc

-- I'd be happy.

What are the alternatives?

[Plamen Ratchev]

You can use OPENQUERY, like:

SELECT * INTO #Tmp FROM OPENQUERY(Loopback, 'EXEC MySp');

Make sure to read Erland Sommarskog's article on some issues with this
approach:
http://www.sommarskog.se/share_data.html#OPENQUERY

Also, if you create the table structure first then you can use INSERT EXEC:

INSERT #Tmp EXEC MySp;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Deane]

Why would you want to store the resultset in a table if you can simply

re-run the sproc?

Because I need to run queries on the resultset, without changing the
stored proc.

Deane