Post reply

SELECT INTO with stored procedure result set?

  • March 26, 2004 at 2:34 pm

    #110821

    Is there any way to Generate a temp table to hold the results of a stored procedure at the same time as executing the Stored procedure? 

    Id like to do something like the following which doesn't seem to work...

    SELECT * INTO #tmp
    EXEC (sp_FOO)
     
    I currently need SQL 7.0 solutions today, but SQL 2000 is in the very near future for me.  Table variable maybe after the upgrade?

    I'm well aware that this (if it's possible) could qualify for the 'worst practices' series......but it sure would ease some early modeling and ad-hoc data requests!

     

  • March 26, 2004 at 3:17 pm

    #500767

    try

    INSERT INTO USED_DISK

     (col1,col2...)

    EXEC dbo.sp_Foo

    Francis

  • March 26, 2004 at 3:40 pm

    #500770

    Thanks for the quick reply fhanlon,

    I do use the INSERT INTO method to good effect.  What I'm hoping for is something like SELECT...INTO that would generate the #tmp table at the same time as it gets the data.  Of course, I'm not sure it's even possible, which I why I've posted here.

  • March 29, 2004 at 9:16 am

    #500899

    I have a couple more questions on this topic.  Essentially I'd like to be able to use existing stored procedures on the fly, joined to other data.  I already use the technique of building a temp table and then using INSERT...EXEC.  I would like to do it without pre-building a temp table or table variable...

    1) If I want to return stored procedure results to a temp table do I have to create the temp table first?  Is Select Into out?

    2) What about @Table Variables in sql 2000?  BOL 2000 says that: SET @tablevar = EXEC(sp_MyStoredProc) doesn't work.  Is there some trick to achieve similar results?

    3) Am I out of luck?

    Thanks in advance for your replies!

  • March 29, 2004 at 3:14 pm

    #500963

    based on my experience you are out of luck in SQL 7.  In SQL2K you would probably want to use Inline Functions which return a table based on the select statement in the function.  It wouldl work something like this:

    CREATE FUNCTION

    dbo.test

    (

    /* @parameter1 datatype = default value,

    @parameter2 datatype

    */ ) RETURNS TABLE

    AS

    RETURN ( Select col1, col2, col3 from test )

    Then in an sp you would run code like this:

    Select

    A.col1,

    B.col2,

    B.col3

    From

    test A Inner Join

    dbo.Test() B On

    A.col1 = B.col1

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • March 30, 2004 at 12:42 am

    #501012

    Thanks Jack,

    As it turns out I'll be moving to SQL 2000 very soon now, so this will become useful almost immediately.  I'm running tests this week.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply