How to Insert Stored Procedure Results into a Temporary Table Without Defining its Structure?

Inserting Stored Procedure Results into a Temporary Table Without Pre-defining Structure

Problem: How can you populate a temporary table with the results of a stored procedure without explicitly defining the table schema beforehand?

Solution: Leverage the OPENROWSET function. This function executes a remote query and directly inserts the results into a temporary table, dynamically inferring the table structure from the query's output.

Here's an example:

<code class="language-sql">-- Sample Stored Procedure
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

-- Insert results into temporary table using OPENROWSET
SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

-- Verify the data
SELECT * FROM #MyTempTable</code>

This code first defines a sample stored procedure (getBusinessLineHistory). Crucially, it then enables "Ad Hoc Distributed Queries" using sp_configure. This is a prerequisite for using OPENROWSET.

OPENROWSET executes the stored procedure remotely, and the returned data is automatically inserted into #MyTempTable. The temporary table's structure is dynamically created based on the data types returned by the stored procedure. Finally, a SELECT statement confirms the data's successful insertion. This approach avoids the need to manually define the temporary table's schema.

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