SQLServerCentral Article

Insert Stored Procedure Results Into New Table Without Using OPENQUERY

,

Overview

In the spirit of code reuse sometimes we would like to capture the results of a stored procedure in a table using INSERT INTO along with EXECUTE. This technique is sometimes described as an INSERT…EXEC statement. A pre-requisite of using INSERT...EXEC is that the table you are inserting into must exist prior to the statement executing and the schema of that table must be consistent with the output interface (i.e. the shape) of all of the stored procedure’s resultsets. Regarding all, INSERT…EXEC can capture multiple resultsets delivered by a single stored procedure execution however all resultsets must have the same shape.

Concerns enter when we do not own the stored procedure code yet we still want to execute it and capture its results into a table. Not owning the stored procedure code means the output interface could later be changed without our knowledge or consent and the only way to insulate our INSERT…EXEC statements from those types of changes is to, at runtime, build the table we are inserting into based on the stored procedure’s output interface at that time.

The Commonly Offered Solution

The solution offered most often to solve this type of problem is to use OPENQUERY to “select” data from the stored procedure into a table using SELECT…INTO. The technique that uses OPENQUERY leverages Linked Server technology to open a new connection to the database where the stored procedure in question resides. When the stored procedure resides on the same instance where the query using OPENQUERY is being used a loopback is created where the context changes from T-SQL, to an external Linked Server connection back to the same database instance, and then back to T-SQL. The Linked Server, by necessity, infers the shape of the resultset that will be returned from the stored procedure and that metadata is then available for use in creating a new table, on the fly, per the behavior of the INTO Clause. Pretty neat stuff actually, and creative. 

SELECT *
INTO #result
FROM OPENQUERY([LOCALHOST], 'EXEC sys.sp_who;');

The technique can be useful but it has several drawbacks. The list of drawbacks, and the details of each, are out of the scope of this article but they are widely known and can be easily researched using an internet search engine. In many instances the use of OPENQUERY is forbidden per a company or departmental policy due to some of these drawbacks. In the next section I will offer an alternative to accomplish the same effect without using OPENQUERY or a Linked Server.

An Alternative Solution

Compile this stored procedure into a database of your choice:

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   OBJECT_ID = OBJECT_ID(N'dbo.alter_table_for_first_result_set_from_object')
                    AND TYPE IN (N'P', N'PC') ) 
    DROP PROCEDURE  dbo.alter_table_for_first_result_set_from_object;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC dbo.alter_table_for_first_result_set_from_object
(
    @object_name SYSNAME,
    @table_name SYSNAME
)
AS
BEGIN
    /********************************************************************************
    Created:    2016-02-25
    Purpose:   Per a stored procedure's metadata this procedure will add columns 
                to a table that resembles the resultset delivered by that stored 
                procedure. This procedure is meant to be used as a precursor to using 
                INSERT...EXEC to capture the results of said stored procedure into 
                said table.
    Author:     Orlando Colamatteo
    Example:
        -- This example adds columns to an existing temporary table named #result that 
        -- enables it to capture the results of a stored procedure using a statement 
        -- like this: INSERT INTO #result EXEC dbo.some_stored_procedure;
        EXEC dbo.alter_table_for_first_result_set_from_object
            @object_name = N'dbo.some_stored_procedure',
            @table_name = N'#result';
    
    Modification History:
    Date        Author          Purpose
    ----------- --------------- ----------------------------------------------------
    ********************************************************************************/    SET NOCOUNT ON;
    -- variable used to store list of columns output by stored procedure
    DECLARE @column_list NVARCHAR(MAX);
    -- get list of columns included in stored procedure resultset from DMV as a single string
    SELECT @column_list = STUFF((SELECT N', ' + QUOTENAME(name) + N' ' + system_type_name + N' NULL'
                                 FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID(@object_name), 0) AS p2
                                 ORDER BY p2.column_ordinal
                                 FOR XML PATH(N''), TYPE).value(N'.[1]', N'NVARCHAR(MAX)'), 1, 2, N'');
    -- return a descriptive error message when the resultset metadata cannot be retrieved
    IF @column_list IS NULL
        THROW 50000, 'Stored procedure resultset metadata could not be retrieved', 1;
    -- add the columns to the requested table
    EXEC (N'ALTER TABLE ' + @table_name + ' ADD ' + @column_list);
END
GO

Next, run this script to see the technique in action, storing the results of sp_who in a temporary table:

-- if temp table exists from a previous run, drop it
IF OBJECT_ID(N'tempdb..#result') IS NOT NULL
    DROP TABLE #result;
-- create the basis for a temp table that will store the results of our stored procedure.
-- more columns will be added to the table later. we create the table declaratively 
-- so it will be in scope when we look to populate it using INSERT..EXEC.
CREATE TABLE #result
(
 -- this column will not interfere with the INSERT...EXEC below, i.e. SQL Server 
 -- will correctly skip attempting to map a column in the proc 
 -- resultset to the IDENTITY column
 result_id INT IDENTITY(1, 1)
               NOT NULL
);
-- before, empty table with only our identity column
SELECT  *
FROM    #result;
-- dynamically add columns to our temp table that can capture the data in the resultset 
-- of the stored procedure we will later call using INSERT...EXEC
EXEC dbo.alter_table_for_first_result_set_from_object
    @object_name = N'sys.sp_who',
    @table_name = N'#result';
-- before, empty temp table with all our columns
SELECT  *
FROM    #result;
-- capture the results
INSERT  INTO #result
        EXEC sys.sp_who;
-- after, with data from our stored procedure
SELECT  *
FROM    #result;

The code comments attempt to explain the flow and purpose. In effect, the code does what OPENQUERY, the Linked Server and the SELECT…INTO do. Namely, the code determines the output interface of the stored procedure from which we want to capture results, the code builds out a table that resembles the stored procedure's output interface and then it pipes the results from the stored procedure into the table using INSERT...EXEC.

Closing Notes

My goal was to offer a simple alternative to using OPENQUERY and a Linked Server to dynamically capture the results of a stored procedure in a table. I hope you agree that I have accomplished that goal. Please join the discussion to offer your thoughts or concerns on the usefulness of the technique I have shared. Thank you for reading.

Disclaimer

The alternative technique presented works on SQL Server 2012 or newer.

References

Rate

4.8 (59)

You rated this post out of 5. Change rating

Share

Share

Rate

4.8 (59)

You rated this post out of 5. Change rating