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.
