How to insert the result of a stored procedure in a table in SQL?

Let’s create a simple stored procedure (SP) with a SELECT query. There are different ways to insert the result of SP into a temporary table according to different scenarios.

CREATE PROC sp_order
AS
BEGIN
    SELECT * FROM orders
END
GO

Scenario 1: If we know the format of the result of SP

In this case, we can create a temporary table that matches the format of the result of the SP. And then use INSERT INTO… EXEC SP syntax as below. This is the simplest approach.

CREATE TABLE #temp_order (
    order_id INT,
    customer_name VARCHAR(100),
    order_date DATE,
    total_orders INT
)

INSERT INTO #temp_order
EXEC sp_order

Scenario 2: If we do not know the format of the result of SP

There can be cases where we don’t know the format of the SP result before calling it. So, creating a temporary table beforehand is not an option. In this case, we need to use OPENROWSET . To be able to use it, we must set the configuration below.

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

Then, we can use OPENROWSET. 

SELECT *
INTO #temp_order2
FROM OPENROWSET('SQLNCLI', 'Server=(YourDBInstanceName)\SQL2019;Trusted_Connection=yes;',
EXEC YourDBName.dbo.sp_order) 

Up Next:

Read How to return all information of a row with MAX or MIN value in a GROUP BY in SQL?