将多个选择查询结果合并到SQL Server中的一个最终表中

I am using SQL Server for database purposes.

我使用SQL Server进行数据库管理。

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. 如果Role = Admin，请执行一些select查询并插入@ table1
3. if Role = Employee, do some select query and insert into @table1
4. 如果Role = Employee，请执行一些select查询并插入@ table1
5. if Role = Accountant, do some select query and insert into @table1
6. 如果Role = Accountant，请执行一些select查询并插入@ table1
7. if Role = Worker, do some select query and insert into @table1
8. 如果Role = Worker，请执行一些select查询并插入@ 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.

我想要一个所有条件明智的列表，所以我一次又一次地调用上面的存储过程条件明智，这是耗时的，所以我想如果我在一个表中一次性将它全部用作最后一列的角色作为角色的名称所以我可以在我即将到来的结果中执行条件，但不知道如何在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.

注意：所有选择查询结果与我创建table1的结果相同。

2 个解决方案

#1

As you have already declared the table @table1

因为你已经声明了表@ table1

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

如果除了您编写的角色之外的所有查询都没有更改，只需将您的select查询与insert插入@ table1。

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.

建议使用第二个，因为通过使用union的muliptle select语句收集结果集并在单个语句中插入更快，而不是多个插入，因此您可以在一个表中获取所有详细信息。

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")

#2

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

创建所有结果集的UNION，然后将其插入@ table1。就像是

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