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

#1 (**permalink**) 05-11-2008, 09:41 PM

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?

#2 (**permalink**) 05-11-2008, 09:41 PM

On Sat, 10 May 2008 20:29:14 -0700 (PDT), Deane
<deane.barker@gmail.com> wrote:

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?

#3 (**permalink**) 05-11-2008, 09:41 PM

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

#4 (**permalink**) 05-13-2008, 07:13 PM

> 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode