Insert stored procedure results into temp table

Question

I have a stored procedure that returns this result:

The way I call the stored procedure is:

Exec uspGetStandardUsingRoleandPhase '1908003'

I want to store these results into a temp table so I used insert into like this:

IF OBJECT_ID(N'tempdb.dbo.#tmp', N'U') IS NOT NULL
    DROP TABLE #tmp

CREATE TABLE #tmp
(
    startDate DATE,
    endDate DATE,
    strPhase NVARCHAR(50),
    strBadgeNumber NVARCHAR(30)
)

INSERT INTO #tmp (startDate, endDate, strPhase, strBadgeNumber)
    EXEC uspGetStandardUsingRoleandPhase '1908003'

But I get an error like this:

INSERT EXEC failed because the stored procedure altered the schema of the target table.

Well you have the error :) now you just need to find what is modifying the schema inside your SP. We can't help without the SP code. — Dale K

squillman squillman · Accepted Answer · 2019-09-26T01:45:39

Hard to say without seeing the code to the stored procedure, but my guess is that the procedure also creates a temp table named #tmp. Try creating a temp table with a different name and running your INSERT EXEC into that, or post the code to the procedure so we can see it.

Insert stored procedure results into temp table

2 Answers