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

 797 Total Views,  4 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 *