How to Insert Stored Procedure Results into a Temporary Table in SQL?

Inserting Stored Procedure Results into a Temporary Table in SQL

SQL's INTO clause readily inserts table data into temporary tables. However, directly inserting results from a stored procedure using EXEC often leads to syntax errors. The solution? Employ the OPENROWSET function.

Here's how to achieve this:

<code class="language-sql">CREATE PROC getBusinessLineHistory
AS
BEGIN
    SELECT * FROM sys.databases
END
GO

-- Enable Ad Hoc Distributed Queries (required for OPENROWSET)
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;</code>

OPENROWSET provides a workaround, enabling access to stored procedure data without explicitly defining the temporary table's columns. This simplifies storing and subsequently processing or analyzing stored procedure output. Note that enabling "Ad Hoc Distributed Queries" is a prerequisite for this method to function correctly.

The above is the detailed content of How to Insert Stored Procedure Results into a Temporary Table in SQL?. For more information, please follow other related articles on the PHP Chinese website!