SQL Server – How to save Stored Procedure result into a temporary table

By | 07/09/2022

In this post, we will see how to save a Stored Procedure result into a temporary table.
We remember that Temporary tables are tables that exist temporarily on SQL Server and, they are used to store result that need to be elaborate.

We start creating a database called TestBlog where, we will add two tables called User and TypeUser, so defined:

[TYPEUSER]

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TypeUser](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Description] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_TypeUser] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO



[USER]

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[User](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Email] [nvarchar](100) NOT NULL,
	[Password] [nvarchar](100) NOT NULL,
	[Type] [int] NOT NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [FK_User_TypeUser] FOREIGN KEY([Type])
REFERENCES [dbo].[TypeUser] ([ID])
GO

ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_TypeUser]
GO



Finally, we run some SQL scripts to add data in the tables:

GO
SET IDENTITY_INSERT [dbo].[TypeUser] ON 
GO
INSERT [dbo].[TypeUser] ([ID], [Description]) VALUES (1, N'User')
GO
INSERT [dbo].[TypeUser] ([ID], [Description]) VALUES (2, N'Reader')
GO
INSERT [dbo].[TypeUser] ([ID], [Description]) VALUES (3, N'Admin')
GO
SET IDENTITY_INSERT [dbo].[TypeUser] OFF
GO
SET IDENTITY_INSERT [dbo].[User] ON 
GO
INSERT [dbo].[User] ([ID], [Email], [Password], [Type]) VALUES (1, N'user1@email.com', N'pass123', 1)
GO
INSERT [dbo].[User] ([ID], [Email], [Password], [Type]) VALUES (2, N'user2@email.com', N'pass456', 1)
GO
INSERT [dbo].[User] ([ID], [Email], [Password], [Type]) VALUES (3, N'user3@email.com', N'pass789', 1)
GO
INSERT [dbo].[User] ([ID], [Email], [Password], [Type]) VALUES (4, N'reader1@email.com', N'psw111', 2)
GO
INSERT [dbo].[User] ([ID], [Email], [Password], [Type]) VALUES (5, N'reader2@email.com', N'psw8u6', 2)
GO
INSERT [dbo].[User] ([ID], [Email], [Password], [Type]) VALUES (6, N'admin1@email.com', N'pas987', 3)
GO
SET IDENTITY_INSERT [dbo].[User] OFF



Now, we will create a Stored Procedure called GetUsersByType to get the list of Users by UserType:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[GetUsersByType]
	@TypeID int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT A.[ID]
      ,A.[Email]
      ,A.[Password]
      ,B.[Description]
	FROM [User] A 
	join [TypeUser] B on A.Type = B.ID
	where A.Type = @TypeID

END
GO



If we run the Stored Procedure, this will be the result:

Now, we create a temporary table called Users, using this SQL command:

create table #Users (ID int, Email nvarchar(100), Password nvarchar(100), UserType nvarchar(50))



Obviously, if we run a select on #Users, this will be the result:


Finally, we will run twice the Stored Procedure passing different TypeIDs and then we will insert the results in #Users:

insert into #Users EXEC GetUsersByType @TypeID=1
insert into #Users EXEC GetUsersByType @TypeID=2


We have done and now, if we run some selects on #Users, these will be the results:



Leave a Reply

Your email address will not be published.