How to Insert Stored Procedure result into a table in Sql Server?

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

13 thoughts on “How to Insert Stored Procedure result into a table in Sql Server?

  1. Nice explained. I’ve a different scenario, here i am having a table with only one column Name, say CREATE TABLE #TMPTAB (Name VARCHAR(32)).

    How do i populate my table from the same stored proc?

    1. First Create a Temp table with the no. of columns equal to the one returned from SP and insert SP result into it. Then from this temp table fetch the required column and insert into a table with one column.

  2. What if the result of a store procedure is two tables and I want the two tables result in one temp table ?

  3. I have seen this solution in a different blog;
    a bit more flexible, don’t need to create the table structure

    select a.* into ##myrep
    FROM OPENROWSET(
    ‘SQLNCLI’,
    ‘Server=Sql01;Trusted_Connection=yes;’,
    ‘SET NOCOUNT ON;SET FMTONLY OFF ;

    EXEC sql01.prod.[dbo].[P_myproc] ‘) AS a;

Leave a Reply

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