What's the easiest way to create a temp table in SQL Server that can hold the result of a stored procedure?
Many times I need to write something like the following when dealing with SQL Server. create table #table_name ( column1 int, column2 varchar(200) ... ) insert into #table_name execute some_stored_procedure; But create a table that has the exact syntax as the result of a stored procedure is a tedious task. For example, the result of sp_helppublication has 48 columns! I want to know whether there is an easy way to do this.
Thanks.
If the procedure to solve SQL server creates a temp table just returns one result set and the ad hoc distributed queries option is enabled.
SELECT * INTO #T FROM OPENROWSET('SQLNCLI', 'Server=(local)MSSQL2008;Trusted_Connection=yes;', 'SET FMTONLY OFF;EXEC sp_who')
Or you can set up a loopback linked server and use that instead.
EXEC sp_addlinkedserver @server = 'LOCALSERVER', @srvproduct = '', @provider = 'SQLNCLI', @datasrc = @@servername SELECT * INTO #T FROM OPENQUERY(LOCALSERVER, 'SET FMTONLY OFF; EXEC sp_who')