Insert results of a stored procedure into a temporary table

Question

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]?

Select all data from BusinessLine into tmpBusLine works fine.

select *

into tmpBusLine

from BusinessLine

I am trying the same, but using a stored procedure that returns data, is not quite the same.

select *

into tmpBusLine

from

exec getBusinessLineHistory '16 Mar 2009'

Output message:

Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'exec'.

I have read several examples of creating a temporary table with the same structure as the output stored procedure, which works fine, but it would be nice to not supply any columns.

Answer 1

If Ad Hoc Distributed queries are not enabled, then use the sp_configure code to enable it.

Have a look at this video to learn Stored Procedure in detail.

Try this code mentioned below:

CREATE PROCEDURE getBusinessLineHistory

AS BEGIN

SELECT * FROM sys1.databases

END

GO

sp_configure 'Show Advanced Options', 1

GO

RECONFIGURE

GO

sp_configure 'Ad Hoc Distributed Queries', 1

GO

RECONFIGURE

GO

SELECT * INTO #MyTemporary_Table FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;', 'EXEC getBusinessLineHistory')

SELECT * FROM #MyTemporary_Table

Or 

You can directly refer to OPENROWSET to insert results of a stored procedures into a temporary table.