Insert Stored Procedure result into Temporary Table in SQL

How to write a SQL Query to Insert Stored Procedure result into Temporary Table in SQL Server with example. For this SQL Interview Question, we are going to use the below shown data

TIP: I suggest you refer both the Stored Procedure and Temporary Table articles to get the basic idea

Data present in the Employee table inside our SQL Tutorial database.

In this example, we are going to use the below shown Stored procedure that will SELECT all the records from the Employee table

-- SQL Stored Procedure - Insert Stored Procedure result into Temporary Table in SQL Example
USE [SQL Tutorial]
GO
 
IF OBJECT_ID ( 'SelectStoredProcedureFirstExample', 'P' ) IS NOT NULL   
    DROP PROCEDURE SelectStoredProcedureFirstExample;  
GO
 
CREATE PROCEDURE SelectStoredProcedureFirstExample
AS
BEGIN
      SET NOCOUNT ON;
      SELECT [FirstName] + ' ' + [LastName] AS [Full Name]
            ,[Education]
            ,[Occupation]
            ,[YearlyIncome]
            ,[Sales]
            ,[HireDate]
      FROM [Employee]
END
GO

OUTPUT

Insert Stored Procedure result into Temporary Table in SQL Example 1

In this example, we will create a Temporary Table. Next, we are going to use the INSERT INTO SELECT Statement to insert the Stored Procedure result into Temporary Table

-- SQL Stored Procedure - Insert Stored Procedure result into Temporary Table in SQL Example
-- Creating Temporary Table
CREATE TABLE #Employee(
	[FullName] [nvarchar](650) NULL,
	[Education] [nvarchar](255) NULL,
	[Occupation] [nvarchar](255) NULL,
	[YearlyIncome] [float] NULL,
	[Sales] [float] NULL,
	[HireDate] [datetime] NULL
)
GO

--Inserting Records from Stored procedure to Temporary Table
INSERT INTO #Employee
EXEC SelectStoredProcedureFirstExample
GO

-- Selecting Records from Temporary Table
SELECT * FROM #Employee

OUTPUT

Insert Stored Procedure result into Temporary Table Example 2

Here, we use the SELECT INTO Statement, and OPENROWSET to insert the Stored Procedure result into Temporary Table

-- SQL Stored Procedure - Insert Stored Procedure results into Temporary Table in SQL Example
sp_configure 'show advanced options', 1;  
RECONFIGURE;
GO 
sp_configure 'Ad Hoc Distributed Queries', 1;  
RECONFIGURE;  
GO 
 
--Inserting Records from Stored procedure to Temporary Table
SELECT * INTO #Employee 
FROM OPENROWSET('SQLNCLI', 
		'Server=(local);Trusted_Connection=yes;',
		'EXEC [SQL Tutorial].dbo.SelectStoredProcedureFirstExample')

-- Selecting Records from Temporary Table
SELECT * FROM #Employee

OUTPUT