How to INSERT specified columns from stored procedure into #tempTable

Question

All of the examples when needing to insert data from a stored procedure do so like this:

INSERT INTO #tempTable
EXEC storedProcedureName @parameter1

I fear what I'm wanting is not possible... pseudo code below of what I'm after. I want to include with my Insert into the TempTable, the parameter(s) I used when calling the stored procedure. It would also be great if I had the option of NOT having to insert ALL of the columns returned by the stored procedure:

INSERT INTO #tempTable (sp_column1, sp_column2, @parameter1)
    EXEC storedProcedureName @parameter1 (column1, column2)

Or perhaps it would look like this using the VALUES keyword:

INSERT INTO #tempTable (sp_column1, sp_column2, @parameter1)
    EXEC storedProcedureName @parameter1 VALUES (column1, column2)

There are a few solutions to this question here: https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table — sql_knievel, Jan 10 '20 at 18:30
@sql_knievel Yeah... I've been all over that post. The answer I see in that post before I put up my question was to use OPENROWSET. I absolutely cannot use OPENROWSET and so I figured I'd post my question in hopes there could be another approach. — Code Novice, Jan 10 '20 at 18:35
Short answer - no. Executing a stored procedure returns a resultset of a fixed number of columns. You could update the rows after executing the stored procedure with the parameters you supplied for execution. You could add or modify default constraints for the "parameter" columns. I can think of other alternatives - all of which require more than your single statement. You could, of course, change the stored procedure to return the parameters as a part of the resultset - but it seems you have passed over this approach. — SMor, Jan 10 '20 at 18:39
@SMor Do you know how one would immediately update the #tempTable after the Stored Proc Insert? Basically... is there some sort of construct to capture the last record that was inserted in order for me to Update it... rather than looking for IDs that exist in the #tempTable... something like UPDATE #tempTable SET = param1 WHERE lastInsertedRecord — Code Novice, Jan 10 '20 at 18:50
you could set up the temp table with default constraint for the extra column that references SESSION_CONTEXT then set that value to the parameter value prior to the insert. So no need to dynamically drop and create defaults — Martin Smith, Jan 10 '20 at 20:20
This entire use case I was needing to create this is mainly for QA work. The Stored Proc was created and compiled... was looking for a nice way to pull ALL valid data in the database that could be used by this report aka Stored Proc... for this I needed to pump into the Stored Proc all of the possible parameters to test that the data being returned looks good and won't break the report. — Code Novice, Jan 10 '20 at 22:18

score 0 · Accepted Answer · edited Mar 22 '20 at 11:52

In order to INSERT from a stored procedure and add/remove columns returned back from that stored procedure I needed to make use of two temp tables. It is not possible to add or choose columns to INSERT when executing from a stored procedure.

The code explained

I wanted to create a generic functioning example simulating the use of a stored procedure. The only difference in this code and my production code is the simulated data I'm using in place of my stored procedure. I managed this using a temp table I named #StoredProc and I inserted into it some fake data with IDs.

1: The first temp table used for the actual proof of concept and for testing is called #Report. It is used to store the default columns being returned by the stored procedure. These columns MUST match exactly 1 to 1 which is in part the issue I am faced with. The stored procedure takes in as its parameter the FieldID however the result set returned does NOT include the FieldID or any other IDs. This is where my issue originally came from as I wanted to store in my temp table both the results along with any parameters I used to obtain that data.

2: The second temp table #ReportwithID is used to store the data from the #Report temp table. When the data is moved over to the #ReportwithID temp table, I am able to include the parameters. When inserting into the #ReportwithID table, I include the FieldID parameter which is being stored inside the @ReportID variable within the cursor while loop. With that I finally have the data I am pulling back from the stored procedure along with any other columns I want to capture... in this instance I am wanting to see each returned record along with the parameters that obtained this data.

Last: when the cursor while loop has completed looping thru all of the IDs and storing the data into the #ReportwithID temp table I SELECT * of the data out of it so I can view it.

The code block below can be run in any SQL Server database assuming you have all of the appropriate permissions to do so such as create temp tables... etc.

/* --- BEGIN Create fake data to simulate the data being pulled back by a stored procedure --- */
DROP TABLE IF EXISTS #StoredProc;

CREATE TABLE #StoredProc
(
    fieldID INT,
    fName   VARCHAR(200),
    lName   VARCHAR(50),
);
GO

INSERT INTO #StoredProc
    SELECT 1 AS fieldID, 'Jimmy'      AS fName, 'Fallon'   AS lName UNION ALL
    SELECT 2 AS fieldID, 'Jason'      AS fName, 'Fick'     AS lName UNION ALL
    SELECT 3 AS fieldID, 'Dilly'      AS fName, 'Dally'    AS lName UNION ALL
    SELECT 4 AS fieldID, 'Jim'        AS fName, 'Jacko'    AS lName UNION ALL
    SELECT 5 AS fieldID, 'Brina'      AS fName, 'Burton'   AS lName UNION ALL
    SELECT 6 AS fieldID, 'Tim'        AS fName, 'McMally'  AS lName UNION ALL
    SELECT 7 AS fieldID, 'Sam'        AS fName, 'Bateman'  AS lName UNION ALL
    SELECT 8 AS fieldID, 'Nelly'      AS fName, 'Newsome'  AS lName UNION ALL
    SELECT 9 AS fieldID, 'J-Schwilly' AS fName, 'McNilly'  AS lName
GO
/* --- END Creating Fake stored procedure data --- */

/* If the temp tables exists we want to remove it before we start inserting records. */
DROP TABLE IF EXISTS #Report;
DROP TABLE IF EXISTS #ReportwithID;

/* Stored procedure Physician Report Fields for Temp Table */
CREATE TABLE #Report
(
    fName VARCHAR(200),
    lName VARCHAR(50),
);

/* Stored procedure Physician Report Fields for Temp Table */
CREATE TABLE #ReportwithID
(
    fieldID INT,
    fName   VARCHAR(200),
    lName   VARCHAR(50),
);

/* Turns Off the Servers return of the Rows affected back to the Client */
SET NOCOUNT ON;

/* --- CURSOR STUFF --- */
/* Declare Variables to store data retrieved back from the Cursor */
DECLARE @RecordID   INT;

/* Declare Cursor */
DECLARE cur_FakeReportData CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY --In order to make my cursors as efficient as possible, Use the following declarations
FOR /* Build up Fake IDs to Loop Over */
    SELECT 1 AS fieldID UNION ALL
    SELECT 2 AS fieldID UNION ALL
    SELECT 3 AS fieldID UNION ALL
    SELECT 4 AS fieldID UNION ALL
    SELECT 5 AS fieldID UNION ALL
    SELECT 6 AS fieldID UNION ALL
    SELECT 7 AS fieldID UNION ALL
    SELECT 8 AS fieldID UNION ALL
    SELECT 9 AS fieldID
;

/* When we are ready to use the Cursor we must first OPEN it */
OPEN cur_FakeReportData;

/* Fetch a row from the cursor into one or more variables */
FETCH NEXT FROM cur_FakeReportData INTO @RecordID;

PRINT 'Fetch_Status: ' + CAST(@@FETCH_STATUS AS Varchar);

/* While Loop: If the Previous Fetch Was successful enter the Loop.
 |  0   The FETCH statement was successful.
 | -1   The FETCH statement failed or the row was beyond the result set.
 | -2   The row fetched is missing.
 | -9   The cursor is not performing a fetch operation.
*/
WHILE @@FETCH_STATUS = 0

    BEGIN
        BEGIN TRY
            PRINT(@RecordID);
            INSERT INTO #Report
            --EXEC report.Report @RecordID;
            SELECT fName, lName FROM #StoredProc WHERE fieldID = @RecordID; /* Simulating pulling data from a STORED Proc */

            INSERT INTO #ReportwithID
            SELECT @RecordID AS fieldID, r.*
            FROM #Report r;

            DELETE FROM #Report;

        END TRY
        BEGIN CATCH
            SELECT  
                  ERROR_NUMBER()    AS ErrorNumber  
                , ERROR_SEVERITY()  AS ErrorSeverity  
                , ERROR_STATE()     AS ErrorState  
                , ERROR_PROCEDURE() AS ErrorProcedure  
                , ERROR_LINE()      AS ErrorLine  
                , ERROR_MESSAGE()   AS ErrorMessage
                , @RecordID         AS RecordID;
        END CATCH

        /* FETCH NEXT Record FROM Cursor */
        FETCH NEXT FROM cur_FakeReportData INTO @RecordID;
    END
;

PRINT 'Fetch_Status: ' + CAST(@@FETCH_STATUS AS Varchar) +' Rows: '+ CAST(@@CURSOR_ROWS AS Varchar)

/* Use the below to View status of ALL Cursors by Session ID */
--SELECT * FROM sys.dm_exec_cursors(@@SPID) -- Use 0 to view ALL Sessions

/* When you are done with the Cursor be sure to CLOSE it */
CLOSE cur_FakeReportData;
/* Deallocate the cursor to release it from memory. */
DEALlOCATE cur_FakeReportData;

--SELECT * FROM #Report;

SELECT *
FROM #ReportwithID
;

How to INSERT specified columns from stored procedure into #tempTable

1 Answers1