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. Select the correct answer from above options

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.