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]

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

On Sat, 10 May 2008 20:29:14 -0700 (PDT), Deane
<deane.barker@g mail.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]

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

You can use OPENQUERY, like:

SELECT * INTO #Tmp FROM OPENQUERY(Loopb ack, '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]

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

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