Insert results of a stored procedure into a temporary table

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.

You can use OPENROWSET for this. Have a look. I've also included the sp_configure code to enable Ad Hoc Distributed Queries, in case it isn't already enabled.

CREATE PROC getBusinessLineHistory
AS
BEGIN
    SELECT * FROM sys.databases
END
GO

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

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

SELECT * FROM #MyTempTable

If you want to do it without first declaring the temporary table, you could try creating a user-defined function rather than a stored procedure and make that user-defined function return a table. Alternatively, if you want to use the stored procedure, try something like this:

CREATE TABLE #tmpBus
(
   COL1 INT,
   COL2 INT
)

INSERT INTO #tmpBus
Exec SpGetRecords 'Params'

In SQL Server 2005 you can use INSERT INTO ... EXEC to insert the result of a stored procedure into a table. From MSDN's INSERT documentation (for SQL Server 2000, in fact):

--INSERT...EXECUTE procedure example
INSERT author_sales EXECUTE get_author_sales