storing Stored Procedure Output

I need to store the output of a stored procedure in #table

the problem is my procedure's output is a table of 70 field

so, I can't create the #table before storing the output of the procedure

because it need alot of effort.

I need solution similar to 开发者_运维问答select * into #table from Table_Name

witch automatic create the #table

I found the solution at the following link :
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=85347 so, to store the output of a stored Procedure we need to use the OPENROWSET() function
but before using it we have to enable show advanced options and
Ad Hoc Distributed Queries Here is the solution :

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO
Select * into #temp 
from OPENROWSET('SQLOLEDB','Data Source=Myserver;Trusted_Connection=yes;Integrated Security=SSPI', 'Execute DB_Name..Prc')

And for the parameters I can add them as string whit procedure revoke statement

'Execute DB_Name..Prc'
         + CAST(parameter1 AS varchar(50)) + .....
         + CAST(parameter(n) AS varchar(50))

I think you want OPENROWSET. Check out: http://msdn.microsoft.com/en-us/library/ms190312.aspx

Please note these examples haven't been tested.

--Example for master.dbo.sp_who
SELECT * INTO #TempSpWho
FROM OPENROWSET ('SQLOLEDB','Server=(local);
                  TRUSTED_CONNECTION=YES;',
                 'set fmtonly off exec master.dbo.sp_who')

Source: http://blogs.technet.com/b/wardpond/archive/2005/08/01/the-openrowset-trick-accessing-stored-procedure-output-in-a-select-statement.aspx

Further, if you have parameters to the SP you will need to write dynamic SQL:

DECLARE @testVariable varchar(10);
SET @testVariable = 'varTest';
EXEC( 'SELECT * INTO #TempSpWho
       FROM
       OPENROWSET(
         ''SQLNCLI'',
         ''Database=dbIDsAndNames;Uid=sa;Pwd=nosecurity;'',
         ''SET FMTONLY OFF
         EXEC [dbo].[mySproc] ''' + @testVariable + ''')') 

Source: http://www.dotnetspider.com/forum/163990-Working-with-Openrowset.aspx

Note: None of the above examples were originally created by me so credit goes to the sources if thy work.

If you are building the temp table manually (ie. not interested in ALL 70 rows) this would be the method:

CREATE TABLE #foo(
  [bar] int,
  baz varchar(123)
)
insert into #foo exec spDoSomething
select * from #foo
drop table #foo

The stored procedure must be using some select * from .. statement inside it to generate this output. You can modify that, right?