how to insert result of stored procedure into temporary table in sql server

 3,162 Total Views,  20 views today

This recipe will explain with Step by step example of how to insert result of a store procedure into temporary table in sql server.

— Create Table
USE adventureworks2012
GO
CREATE TABLE Employee(
EmployeeID int NOT NULL,
FirstName varchar(50) NOT NULL,
MiddleName varchar(50</b) NULL,
LastName varchar(50) NOT NULL
)
GO

—– Insert Data into table
insert into Employee
select 1,’Bryan’,’Adam’,’Baker’
go
insert into Employee
select 2,’Nancy’,null,’Anderson’
go
insert into Employee
select 3,’Simon’,’Karan’,’Rapier’
go

——- Create Procedure
Create PROCEDURE Get_Person_Name
AS
SELECT EmployeeID,FirstName, MiddleName, LastName
FROM Employee
GO

——- Create Temp Table
CREATE TABLE #result(
EmployeeID int ,
FirstName varchar(50),
MiddleName varchar(50),
LastName varchar(50)
)
——- Insert Store Procedure result to temp table
INSERT INTO #result
EXEC Get_Person_Name

— Select data from temp table
SELECT *
FROM #result;

—– drop temp table
drop table #result

Leave a Reply

Your email address will not be published. Required fields are marked *