CODEWITHSUNDEEP
sqlsql-serverstored-procedurestemp-tables
guradio
guradio

Reputation: 15555

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.

Upvotes: 10

Views: 31036

Answers (2)

Mark Sowul
Mark Sowul

Reputation: 10600

Worth noting this can also come up in SQL 2016 if you're using Query Store, and it happens to run a clean up during the procedure's execution. They suggest increasing the Query Store size to reduce the likelihood, but other than that, they're not planning to fix it for SQL 2016 (it's fixed in 2017)

https://support.microsoft.com/en-us/help/4465511/error-556-insert-exec-failed-stored-procedure-altered-table-schema

Upvotes: 1

squillman
squillman

Reputation: 13641

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.

Upvotes: 23

Related Questions