Join us in Las Vegas on June 13-16 for the Snowflake Summit

Learn More
datameer banner faq

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

  • How-Tos FAQs
  • December 16, 2018

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

A Faster Way To Build Data Models

Mix SQL and no code to create data models in a few clicks vs months.

Try Datameer Today

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?

More Resources We Think You Might Like

SQL FAQ Feat

How to concatenate data from different rows of ...

Let us consider the following table: CREATE TABLE fruits (     ID INT,     NAME VARCHAR(50) ) INS...

  • How-Tos FAQs
  • December 16, 2018
SQL FAQ Feat

How to return all information of a row with MAX...

Suppose we have a table “score” as below, and we need all information on a row with the maximum s...

  • How-Tos FAQs
  • December 16, 2018
SQL FAQ Feat

How to select the first row of each GROUP BY in...

Let us create a table ‘orders’ to replicate the problem. CREATE TABLE orders (       ...

  • How-Tos FAQs
  • December 16, 2018