Below is the syntax to insert records into a temporary table from the output of a stored procedure.
CREATE TABLE #tmpTable ( COL1 INT, COL2 INT ) INSERT INTO #tmpTable Exec sp_GetRecords @Param1 = 'Params'
Note, because we can’t specify columns in the EXEC, the temp table signature should match the stored procedures output.
Personally I have found this syntax helpful in a master procedure which then called other procedures. It would build a temp table and then select the results at the end.
Helpful if you are trying to optimise a procedure and have difficulties getting the best execution plan.