Insert Stored Procedure Results Into New Table Without Using OPENQUERY

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    Comments posted to this topic are about the item Insert Stored Procedure Results Into New Table Without Using OPENQUERY

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • Mike Is Here

    Hall of Fame

    Points: 3348

    You offer a very creative solution that has the ability to have dynamic columns. However, practically since you almost always need to know the columns names any way to work with the results wouldn't the following be more straight forward

    CREATE TABLE #results

    (

    spid INT

    , ecid INT

    , status VARCHAR(255)

    , loginname VARCHAR(128)

    , hostname VARCHAR(128)

    , blk INT

    , dbname VARCHAR(128)

    , cmd VARCHAR(255)

    , request_id INT

    )

    INSERT INTO #results

    ( spid ,

    ecid ,

    status ,

    loginname ,

    hostname ,

    blk ,

    dbname ,

    cmd ,

    request_id

    )

    EXEC sys.sp_who;

    SELECT * FROM #results

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    Mike Is Here (3/22/2016)


    You offer a very creative solution that has the ability to have dynamic columns. However, practically since you almost always need to know the columns names any way to work with the results wouldn't the following be more straight forward

    CREATE TABLE #results

    (

    spid INT

    , ecid INT

    , status VARCHAR(255)

    , loginname VARCHAR(128)

    , hostname VARCHAR(128)

    , blk INT

    , dbname VARCHAR(128)

    , cmd VARCHAR(255)

    , request_id INT

    )

    INSERT INTO #results

    ( spid ,

    ecid ,

    status ,

    loginname ,

    hostname ,

    blk ,

    dbname ,

    cmd ,

    request_id

    )

    EXEC sys.sp_who;

    SELECT * FROM #results

    Thanks for the feedback.

    Consider the case where you do not own the procedure code (e.g. sys.sp_who, a stored procedure in a third-party vendor's database, a stored procedure in a database owned by a different development team, etc.) and a new column is added to the output interface of that stored procedure without you being notified, after your code has been deployed in production. The code that follows the pattern where the table is declared up front will encounter this error:

    [font="Courier New"]Msg 213, Level 16, State 7, Procedure some_stored_procedure, Line 5

    Column name or number of supplied values does not match table definition.[/font]

    Using the technique I showed, since the code should not need to reference the new column to function properly given it was produced before the column was added, the code will more readily withstand change. This behavior is also true of columns added at any ordinal position within the resultset, not only at the end.

    I agree declaring the table up front is a straightforward approach and I use it where I own both sides of the code. It will perform better in the large and is a bit simpler to follow. The technique I showed is an alternative approach that offers some advantages over using OPENQUERY to do things dynamically but I concede it introduces some drawbacks as well.

    One other area where I will use the dynamic approach is when I am too lazy to gather the necessary metadata to be able to build the declared table up front, e.g. when I am doing admin work and want to quickly capture the results of a stored procedure into a table.

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • gshouse

    Say Hey Kid

    Points: 663

    If you want to make this super-robust a guard that tests if sys.dm_exec_describe_first_result_set_for_object can determine the metadata could be worthwhile. For example, using this technique for sp_who2 does not work:

    error_number error_severity error_state error_message error_type error_type_desc

    11526 16 1 The metadata could not be determined because statement 'delete #tb1_sysprocesses

    where lower(status) = 'sleeping'

    and upper(cmd) in (' in procedure 'sp_who2' uses a temp table. 10 TEMPORARY_TABLE

  • Fernando Margueirat-273899

    SSC Enthusiast

    Points: 179

    Orlando Colamatteo (3/22/2016)


    Mike Is Here (3/22/2016)


    You offer a very creative solution that has the ability to have dynamic columns. However, practically since you almost always need to know the columns names any way to work with the results wouldn't the following be more straight forward

    This is what the article is trying to prevent.

    FM

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    gshouse (3/22/2016)


    If you want to make this super-robust a guard that tests if sys.dm_exec_describe_first_result_set_for_object can determine the metadata could be worthwhile. For example, using this technique for sp_who2 does not work:

    error_number error_severity error_state error_message error_type error_type_desc

    11526 16 1 The metadata could not be determined because statement 'delete #tb1_sysprocesses

    where lower(status) = 'sleeping'

    and upper(cmd) in (' in procedure 'sp_who2' uses a temp table. 10 TEMPORARY_TABLE

    Agreed. Sometimes, no matter which technique we choose, the stored procedure will defy any dynamic handling. sp_who2 is one of those cases where we must declaratively define the table we want to insert into. Thanks for making the point.

    As for making the code super-robust, I agree it can detect this case and return a nicer error message. I have submitted an edit for the stored procedure code in the article to add a check and display a nicer error message.

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • hakim.ali

    SSCarpal Tunnel

    Points: 4237

    Nice trick, thanks for sharing.

    Hakim Ali
    www.sqlzen.com

  • Jeff Moden

    SSC Guru

    Points: 995662

    To quote Phil McCracken...

    "SQL Spackle" is a collection of short articles written based on multiple requests for similar code or to share short methods for getting certain things done. These short articles are NOT meant to be complete solutions. Rather, they are meant to "fill in the cracks."

    --Phil McCracken

    The only thing the author didn't do was to explain that fact clearly enough until he got to the "Conclusion" of the article and I consider this article to be an awesome "SQL Spackle" article even if that's not clearly stated up front or in the title.

    To be honest, I can't see why anyone would rate this article at less than "Must Read (5 stars)". It's an introduction to a powerful alternative to OPENROWSET whether a Linked Server is used or not. It's a highly effective "basic tool" that has been introduced and, like any other good tool, opens up possibilities that one may have not previously thought of. And, like any other basic tool in the world of computers, "Computers are an imagination-limited tool. If you have a limited imagination, it will be a limited tool. -- Jeff Moden circa 1980".

    For example, it's been suggested that it's much more effective to actually find out what the return meta data consists of and to hard code the creation of the table. I couldn't agree more but let me ask you... how did people do that in the past? By reading documentation (not always available), deep diving the code in the proc, by running a hard-coded OPENROWSET (it can't take dynamic parameters), or by (gasp!) writing some dynamic SQL around OPENROWSET. You couldn't put it into a general purpose function because OPENROWSET can't take dynamic parameters and you can't execute dynamic SQL within a function without some other grand and totally non-obvious methods that also use OPENROWSET.

    This article explains a method that you could easily encapsulate in a utility function to make it possible to easily determine what the meta data is and to write the CREATE TABLE statement for you if that's what you desired.

    The article also doesn't explain how to solve the travesty produced by some stored procedures such as the problem with 2 columns being returned with the same name as sp_WHO2 does... use your imagination because this basic tool allows for a solution where even OPENROWSET does not. 😉

    The technique in this article is a tool and, like any other tool, it requires your brain and imagination to use it. The article was never intended to present a panacea set of solutions for these kinds of problems. It was meant to present a tool for the imagination.

    My only bitch about the article is that I couldn't give it 10 stars. 😀

    @Orlando,

    Very well done. Thanks for taking the time to write up this very useful and awesome technique. Heh... it took some imagination. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • qbrt

    SSCrazy

    Points: 2422

    Great read and thanks for taking the time to write it up and sharing.

    Getting Jeff Moden's blessing observation and support is fantastic, as I value his opinions tremendously.

    (modified: see Jeff's response below :-D)

  • Jeff Moden

    SSC Guru

    Points: 995662

    qbrt (3/22/2016)


    Great read and thanks for taking the time to write it up and sharing.

    Getting Jeff Moden's blessing and support is fantastic, as I value his opinions tremendously.

    Thanks for the feedback but it wasn't a "blessing"... Rather, it was an "observation". The author did this on his own and it opened up some possibilities for me, as well. 🙂

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Fernando Margueirat-273899

    SSC Enthusiast

    Points: 179

    This article is GREAT! I read it on the train on my way to the office, I couldn't wait to get to the office (:w00t: yes I said that) to try it. I even tried to login remotely to my desk to try it, but my signal on a moving train was not stable enough.

    FM

  • the sqlist

    SSCrazy

    Points: 2398

    This doesn' work:

    SELECT

    *

    INTO #result

    FROM

    OPENQUERY([LOCALHOST], 'EXEC sys.sp_who;')

    select * from #result

    Msg 7202, Level 11, State 2, Line 2

    Could not find server 'LOCALHOST' in sys.servers. ..

    This one, however, works:

    SELECT

    *

    INTO #result

    FROM

    OPENROWSET('SQLNCLI', 'server=(local);trusted_connection=yes',

    'set fmtonly off; exec sp_who;')

    select * from #result

    If you replace sp_who with sp_who2 it doesn't work, not even with the solution in the article. For the second query:

    Msg 11526, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1

    The metadata could not be determined because statement 'delete #tb1_sysprocesses

    where lower(status) = 'sleeping'

    and upper(cmd)' in procedure 'sp_who2' uses a temp table.

    For the article query:

    (0 row(s) affected)

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'ADD'.

    (0 row(s) affected)

    Msg 8101, Level 16, State 1, Line 32

    An explicit value for the identity column in table '#result' can only be specified when a column list is used and IDENTITY_INSERT is ON.

    Don't just give the hungry man a fish, teach him how to catch it as well.

    the sqlist

  • ScottPletcher

    SSC Guru

    Points: 98337

    Excellent article! My only additional suggestion would be to put your code in the master db, and start the proc name with sp_, so that the code can easily be called from any db / db context.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • the sqlist

    SSCrazy

    Points: 2398

    And because the whole "trick" is using the dm_exec_describe_first_result_set_for_object function I directly tried it with sp_who and sp_who2 and again, for sp_who2 doesn't work, it returns on empty row with the error:

    select * from sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('sys.sp_who2'), 0)

    The metadata could not be determined because statement 'delete #tb1_sysprocesses

    where lower(status) = 'sleeping'

    and upper(cmd)' in procedure 'sp_who2' uses a temp table.

    So I guess there is no difference between using this or what I have been using for a log time now:

    SELECT

    *

    INTO #result

    FROM

    OPENROWSET('SQLNCLI', 'server=(local);trusted_connection=yes',

    'set fmtonly off; exec sp_who2;')

    select * from #result

    Don't just give the hungry man a fish, teach him how to catch it as well.

    the sqlist

  • Jeff Moden

    SSC Guru

    Points: 995662

    Damned Temp Tables... they're both the boon and the bane of stored procedures. :pinch:

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 43 total)

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