Record Resultset from Stored Procedure in a Table

There may be times when a Store Procedure outputs a Rowset and you may want to capture the results in a database table.

(The below example is executed on the AdventureWorks database)

Here is how you can do that. Suppose you have a Stored Procedure named LoadEmployee and it accepts the three parameters MaritalStatus, Gender and SalariedFlag and it outputs the resultset from the HumarResources.Employee table. Now, if you are interested in finding all salaried single ladies from the table and store it in EmployeeMaster table, this is how you do..

INSERT INTO dbo.EmployeeMaster
EXEC dbo.LoadEmployee @MaritalStatus = 'S', @Gender = 'F', @SalariedFlag = 1

And the output will be stored in a table.