Skip to content
Advertisement

Insert stored procedure results into temp table

I have a stored procedure that returns this result:

enter image description here

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.

Advertisement

Answer

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.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement