If the results table of your stored proc is too complicated to type out the "create table" statement by hand, and you can't use OPENQUERY OR OPENROWSET, you can use sp_help to generate the list of columns and data types for you. Once you have the list of columns, it's just a matter of formatting it to suit your needs.
Step 1: Add "into #temp" to the output query (e.g. "select [...] into #temp from [...]").
The easiest way is to edit the output query in the proc directly. if you can't change the stored proc, you can copy the contents into a new query window and modify the query there.
Step 2: Run sp_help on the temp table. (e.g. "exec tempdb..sp_help #temp")
After creating the temp table, run sp_help on the temp table to get a list of the columns and data types including the size of varchar fields.
Step 3: Copy the data columns & types into a create table statement
I have an Excel sheet that I use to format the output of sp_help into a "create table" statement. You don't need anything that fancy, just copy and paste into your SQL editor. Use the column names, sizes, and types to construct a "Create table #x [...]" or "declare @x table [...]" statement which you can use to INSERT the results of the stored procedure.
Step 4: Insert into the newly created table
Now you'll have a query that's like the other solutions described in this thread.
DECLARE @t TABLE
(
--these columns were copied from sp_help
COL1 INT,
COL2 INT
)
INSERT INTO @t
Exec spMyProc This technique can also be used to convert a temp table (#temp) to a table variable (@temp). While this may be more steps than just writing the create table statement yourself, it prevents manual error such as typos and data type mismatches in large processes. Debugging a typo can take more time than writing the query in the first place.
If you're lucky enough to have SQL 2012 or higher, you can use dm_exec_describe_first_result_set_for_object
I have just edited the sql provided by gotqn. Thanks gotqn.
This creates a global temp table with name same as procedure name. The temp table can later be used as required. Just don't forget to drop it before re-executing.
declare @procname nvarchar(255) = 'myProcedure',
@sql nvarchar(max)
set @sql = 'create table ##' + @procname + ' ('
begin
select @sql = @sql + '[' + r.name + '] ' + r.system_type_name + ','
from sys.procedures AS p
cross apply sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r
where p.name = 'myProcedure'
set @sql = substring(@sql,1,len(@sql)-1) + ')'
execute (@sql)
execute('insert ##' + @procname + ' exec ' + @procname)
end