David's Blog

What if you want to have the output from a stored procedure written to a temporary table, but you don’t want to have to worry about defining the temp table beforehand? Unfortunately, SQL Server doesn’t have an EXECUTE […]INTO [#temp_table] syntax, or similar. However, as a workaround, you can use SELECT INTO and OPENQUERY (looping-back to the same server) to achieve the desired result:

SELECT *
      INTO #who
      FROM OPENQUERY(YOUR_SERVER_NAME, ‘EXEC sp_who’)

You’ll need to define your server as a linked server for the above to work.

This technique is also a useful way of figuring out the data-types returned by a particular stored procedure:

SELECT TOP 0 *
      INTO #who
      FROM OPENQUERY(YOUR_SERVER_NAME, ‘EXEC sp_who’)

EXEC tempdb..sp_columns ‘#who’