how to combine results of several stored procedures into a single temporary table RRS feed

  • Question

  • There are several existing stored procedures (which I cannot modify) and their resultsets have identical column structures/datatype definitions.  I would like to combine their results in a single temporary table inside a new stored procedure.  I can consume them and merge their results client-side but am running into obstacles when trying to do this server-side.

    Important fact: each of those stored procedures makes use of temporary tables and the resultset of each is returned by a select against a temporary table.  Another important fact: Select * from OPENQUERY(server, 'exec sp') does not work if sp's resultset is produced by  "select  ... from #tmp".

    Is there any way for my new stored procedure to be a consumer of the those existing stored procedures and merge their resultsets?


    Saturday, January 12, 2013 3:53 PM


All replies

  • Do you mean all the SPs in same server or multiple servers?
    Saturday, January 12, 2013 4:13 PM
  • Create a table or temporary table in the sp output format.

    The results can be consumed like:

    INSERT yournewtable

    EXEC yourstoredproc

    Kalman Toth SQL 2008 GRAND SLAM
    Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    • Proposed as answer by Naomi N Sunday, January 13, 2013 4:56 PM
    • Marked as answer by lloydmalvern Monday, January 14, 2013 12:42 PM
    Saturday, January 12, 2013 4:25 PM
  • You should not use OPENQUERY anyway.

    I have an article on my web site that covers this situation. It seems that INSERT-EXEC is the best option for you. But my article covers a few more alternatives.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Sunday, January 13, 2013 4:49 PM
    Saturday, January 12, 2013 4:54 PM
  • Excellent article, Erland. Thanks very much.
    Saturday, January 12, 2013 10:24 PM
  • Thank you, Kalman. I have been trying for two days to mark your reply as The Answer, but IE9 keeps going off into Never Never Land when I click the button.  Now I am trying to append a reply to my original question to see if that works.

    Monday, January 14, 2013 12:39 PM
  • If the procedures already use insert into exec then unfortunately you may be out of luck as INSERT INTO EXEC construct can not be nested....

    For every expert, there is an equal and opposite expert. - Becker's Law

    My blog

    Monday, January 14, 2013 12:44 PM