How to Insert the Result sets of a Stored Procedure/Functions into a Temporary Table in SQL Server




 

In some cases with SQL Server, there may be an instance where you wish to take the resulting data from a stored procedure and insert it into a temporary table for use in another query. Determining how to accomplish this task can be somewhat difficult, so we’ll briefly outline a couple options, depending on your specific needs and database configuration.

Using the OPENROWSET Statement

 

One possibility is to use the OPENROWSET statement, which allows you to access remote data from an OLE DB source and can be executed directly from within another SQL statement. OPENROWSET is a one-time connection and data retrieval method, so it should not be utilized for frequent connections (linking servers is preferable in that case).

 

OPENROWSET can be the target of any INSERT, DELETE, or UPDATE statement, which makes it ideal for our purposes of “executing” our stored procedure for us and extracting that data back out to our waiting temporary table. Also supports bulk operations through a built-in BULK provider that enables data from a file to be read and returned as a rowset.

 

Before using OPENROWSET, it may be necessary to modify some configuration options, specifically by allowing ad hoc access. This can be configured using the following statements:

Now we can utilize OPENROWSET, which has a particular syntax that must be adhered to:

OPENROWSET (

  <PROVIDER_NAME>,

  <DATA_SOURCE>,

  <OPTIONS>

)

Step 1: Enable Ad Hoc Distributed Queries

sp_configure 'Show Advanced Options', 1

GO

RECONFIGURE

GO

sp_configure 'Ad Hoc Distributed Queries', 1

GO

RECONFIGURE

GO

 

Step 2: Insert Results of Stored Procedure/Functions into a Temporary Table

-- Create a Stored Procedure using AdventureWorksLT2012 database

CREATE PROCEDURE GetShippedOrder

AS

SELECT a.[SalesOrderID], a.CustomerID, c.[Name],b.[OrderQty],a.[ShipDate]

    FROM [SalesLT].[SalesOrderHeader] a INNER JOIN [SalesLT].[SalesOrderDetail] b

        ON a.[SalesOrderID] = b.[SalesOrderID]

        INNER JOIN [SalesLT].[Product] c ON b.ProductID = c.ProductID

 

GO

-- Execute Stored Procedure

EXEC GetShippedOrder

GO

 

 

 

-- Create a Inline Function

 

CREATE FUNCTION GetShippedOrders()

RETURNS TABLE

AS

RETURN SELECT a.[SalesOrderID], a.CustomerID, c.[Name],b.[OrderQty],a.[ShipDate]

    FROM [SalesLT].[SalesOrderHeader] a INNER JOIN [SalesLT].[SalesOrderDetail] b

        ON a.[SalesOrderID] = b.[SalesOrderID]

        INNER JOIN [SalesLT].[Product] c ON b.ProductID = c.ProductID

    WHERE a.[ShipDate] IS NULL

GO

 

-- Run the Function

 

select * from  GetShippedOrders()

 

Step 3: Insert into Temp Table

-- Insert into Temp Table from Stored Procedure

 

SELECT *

INTO #TempTable

FROM OPENROWSET('SQLNCLI', 'Server=Amitava-PC;Trusted_Connection=yes;','EXEC [AdventureWorksLT2012].[dbo].GetShippedOrder')

GO

 

-- Select Data from Temp Table

 

SELECT *

FROM #TempTable

GO

 

-- Insert into Temp Table from Inline Function

SELECT *

INTO #TempTable

FROM OPENROWSET('SQLNCLI','Server=AMITAVA-PC;Trusted_Connection=yes;','select * from  [AdventureWorksLT2012].[dbo].GetShippedOrders()')

GO

 

-- Select Data from Temp Table

 

SELECT *

FROM #TempTable

GO