Friday, July 19, 2013

Insert into a table from stored proc

I never realized until today that the results from a stored proc can be inserted into a table. For example, I am selecting employeeID and ManagerID from Adventureworks

select EmployeeID, ManagerID
from HumanResources.Employee

Then create a stored procedure

create procedure dbo.EmpTest
as
select EmployeeID, ManagerID
from HumanResources.Employee

The results of the stored proc exec EmpTest can be used to insert into a table as follows.

declare @test table
(
    EmployeeID int,
    ManagerID int
)

insert into @test
exec EmpTest

select * from @test