Today, I was needed to dump the Stored Procedure result into a temp table.  Below example illustrates how this can be achieved:

First let us create a stored stored procedure which returns result set:

CREATE DATABASE DEMOSQLHINTS
GO
USE DEMOSQLHINTS
GO
CREATE TABLE dbo.Employee(ID INT IDENTITY(1,1),Name VARCHAR(50))

INSERT INTO dbo.Employee(Name)
VALUES('BASAVARAJ BIRADAR'),
('SHREE BIRADAR')
GO

CREATE PROCEDURE dbo.GetEmployees
AS
BEGIN
SELECT * FROM dbo.Employee WITH(NOLOCK)
END
GO
EXEC dbo.GetEmployees
GO

Result:

ID Name
———– ————————————
1 BASAVARAJ BIRADAR
2 SHREE BIRADAR

(2 row(s) affected)

Below script shows how to move the result of the SP GetEmployees to a temporary table #TempEmployees

-- First create the table to which we want to push the SP result
CREATE TABLE #TempEmployees(EmpId int,EmpName Varchar(50))
GO
-- Insert result from the SP to temp table
INSERT INTO #TempEmployees
EXEC dbo.GetEmployees
GO
--Verify the Insert records
SELECT * FROM #TempEmployees

Result:

EmpId EmpName
———– ————————————————–
1 BASAVARAJ BIRADAR
2 SHREE BIRADAR

Let us see another example where we Insert result from a Stored Procedure with Parameter into a temporary table

--First create a stored procedure with parameter
CREATE PROCEDURE GetEmployeeDetails
(@id AS INT)
AS
BEGIN
SELECT * FROM dbo.Employee (NOLOCK)
WHERE Id = @id
END
GO
--Execute SP with parameter and dump the result into a temp table
INSERT INTO #TempEmployees
EXEC GetEmployeeDetails 2

GO
-- Verify the inserted records
SELECT * FROM #TempEmployees

Result:

EmpId EmpName
———– ————————-
2 SHREE BIRADAR

Note: All the examples in this article are tested on Sql Server 2008 version