Insert Stored Procedure result into Temporary Table in SQL

How to write a Query to Insert Stored Procedure result into Temporary Table in SQL Server with example. For this Interview Question, we are going to use the below shown data. Data present in the Employee table inside our Tutorial database.

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

-- Insert Stored Procedure result into Temporary Table Example
 
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

Messages
--------
Command(s) completed successfully.

TIP: I suggest you refer to both the SQL Stored Procedure and SQL Temporary Table articles to get a basic idea. Also refer to the SQL SELECT Statement.

Insert Stored Procedure result into Temporary Table in SQL Example 1

In this Server 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 the temporary table. Please refer to the SQL INSERT INTO SELECT Statement from the SQL Server tutorial.

--  Insert Stored Procedure result into Temporary Table 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

Insert Stored Procedure result into Temporary Table Example 2

In this Frequently Asked Question, we use the SELECT INTO Statement and OPENROWSET to insert the Stored Procedure result into a temporary table. Refer to the SQL SELECT INTO Statement.

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 dbo.SelectStoredProcedureFirstExample')

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