How do I do a SELECT * INTO [temp table] FROM [stored procedure]?
In SQL 2012 or higher, you can use dm_exec_describe_first_result_set_for_object.
DECLARE @schemaname NVARCHAR(255) = 'myschema'
DECLARE @procname NVARCHAR(255) = 'my_sp'
DECLARE @tempname NVARCHAR(255) = 't_sp'
DECLARE @sql NVARCHAR(max)
SET @sql = 'create table ' + @tempname + ' ('
SELECT @sql = @sql + '[' + R.name + '] ' + ISNULL(R.system_type_name, 'varchar(250)') + ','
FROM sys.procedures AS P
JOIN sys.schemas AS S ON S.schema_id = P.schema_id
CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(P.object_id, 0) AS R
WHERE S.name = @schemaname
AND P.name = @procname
SET @sql = substring(@sql, 1, len(@sql) - 1) + ')'
PRINT @sql
-- create a temporary table @tempname
EXECUTE (@sql)
EXECUTE ('insert ' + @tempname + ' exec myschema.my_sp id')