Store Stored Procedure Results In Temp Table SQL Server.

This code example uses SQL Server 2014 Standard

Sometimes we need to insert the values from stored procedures into a temporary table, in this blog I will show you one way to achieve this, the table used is the same from previous blogs and can be found here

To start let's create our stored procedure.

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: 
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE SpForTempData
	-- Add the parameters for the stored procedure here
	@personId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT [FirstName],[LastName],[CityName],[PostCode]
	FROM [dbo].[PersonNameAndAddress]
	WHERE [PersonId] = @personId
END

GO

Create Temp Table

Now that we have our stored procedure, we can create the #tempTable as the code below shows.

CREATE TABLE #tmpPersonNameAndAddress
(
   FirstName nvarchar(100),
   LastName	 nvarchar(100),
   CityName	 nvarchar(100),
   PostCode	 nvarchar(100),
)

INSERT INTO #tmpPersonNameAndAddress
Exec SpForTempData '1'

SELECT * FROM #tmpPersonNameAndAddress

If all goes to plan, you should see the result set as the image below shows.

Store Stored Procedure Results In Temp Table SQL Server

User-Defined Function

Another way to achieve the same results is to use a user-defined function as an inline table, the code for how to do this is below.

-- ================================================
-- Template generated from Template Explorer using:
-- Create Inline Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: 
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION UdfForTempData
(	
	-- Add the parameters for the function here
	@personId int
)
RETURNS TABLE 
AS
RETURN 
(
	-- Add the SELECT statement with parameter references here
	SELECT [FirstName],[LastName],[CityName],[PostCode]
	FROM [dbo].[PersonNameAndAddress]
	WHERE [PersonId] = @personId
)
GO

Now we can use SELECT * FROM UdfForTempData(2) and return the same results set as shown below.

UDF User defined function

In this blog, I have shown two ways to insert results into a  temporary table in SQL Server.

I hope you found this blog helpful and thanks for reading.