0How 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 (
,
,
)
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.
