Insert results of a stored procedure into a temporary table
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 getBusinessLineHistoryASBEGIN SELECT * FROM sys.databasesENDGOsp_configure 'Show Advanced Options', 1GORECONFIGUREGOsp_configure 'Ad Hoc Distributed Queries', 1GORECONFIGUREGOSELECT * 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 #tmpBusExec 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 exampleINSERT author_sales EXECUTE get_author_sales