Combine multiple select query result into one final table in SQL Server

Question

I am using SQL Server for database purposes.

I have a stored procedure that returns the select query of selected data in it.

Example:

DECLARE @table1 TABLE
    (
      UserId int, 
      FullName varchar(200),
      FirstName varchar(200),
      LastName varchar(200),
      Status varchar(10),
      Role varchar(50)
    )

I have used this to return condition wise select query data like:

1. if Role = Admin, do some select query and insert into @table1
2. if Role = Employee, do some select query and insert into @table1
3. if Role = Accountant, do some select query and insert into @table1
4. if Role = Worker, do some select query and insert into @table1

I want a list of all but condition wise so I call above stored procedure again and again condition wise it is time consuming so I thought that I if I take it all at once in one table with Role at last column as name of Role so I can do where condition in my coming result but don't know how to take all in one table that is table1.

If anyone having idea how to do that it is helpful for me so answer it please!

NOTE: All the select query result is same as I have created the table1.

Answer 1

As you have already declared the table @table1

Just write your select query with insert into @table1 if no change in all the queries except the role as you have written.

Example:

Insert into @table1
your query where Role = Admin
Insert into @table1
your query where Role = Employee
Insert into @table1
your query where Role = Accountant
Insert into @table1
your query where Role = Worker

Or you can use below as well (recommended - Jibin's answer)

Insert into @table1
your query where Role = Admin
UNION ALL
your query where Role = Employee
UNION ALL
your query where Role = Accountant
UNION ALL
your query where Role = Worker

second is recommended because good to collect the resultset via muliptle select statement with union and insert in a single statement which is faster rather than multiple insert so by this you can get all the details in one table.

At back-end side means server side you can than take role wise data using entity framework or any method what ever you prefer to use with:

ex using entity framework:

var data = db.storedprocedurename("parameter").ToList();
var adminData = data.where(r => r.Role == "Admin")
var accoutantData = data.where(r => r.Role == "Accountant")
var employeeData = data.where(r => r.Role == "Employee")
var workerData = data.where(r => r.Role == "Worker")

Answer 2

Make a UNION of all the result set and then insert it to @table1. Something like

Insert into @table1
your 1st select query with extra column role
UNION ALL
your 2nd select query with extra column role
UNION ALL
your 3rd select query with extra column role
UNION ALL
your 4th select query with extra column role