What's the easiest way to create a temp table in SQL Server that can hold the result of a stored procedure?
If the procedure 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')
In SQL Server 2012 and above, you can use sys.dm_exec_describe_first_result_set
locally, assuming the result set you are after is the first result:
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += ',' + CHAR(13) + CHAR(10) + CHAR(9)
+ name + ' ' + system_type_name
FROM sys.dm_exec_describe_first_result_set('sp_who', NULL, 1);
SELECT @sql = N'CREATE TABLE #f
(' + STUFF(@sql, 1, 1, N'') + '
);';
PRINT @sql;
Result:
CREATE TABLE #f
(
spid smallint,
ecid smallint,
status nchar(30),
loginame nvarchar(128),
hostname nchar(128),
blk char(5),
dbname nvarchar(128),
cmd nchar(16),
request_id int
);
Note there is a limitation: if your stored procedure creates #temp tables, the metadata functionality does not work. This is why I did not use sp_who2. :-)
No. The result of a stored procedure can vary wildly: it isn't designed to always return exactly one result set like a SELECT on some object.
You have to execute CREATE TABLE