Insert results of a stored procedure into a temporary table Insert results of a stored procedure into a temporary table sql-server sql-server

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