Inserting collums data from 3 different tables into new table

Question

I have 3 table,   table: "db.Users" has columns: UserId, FirstName, Lastname, Email   table: "db.Roles" has columns:  RolesId, RoleName    Table: "db.UserRoles"  Has columns:  UserId, RolesId   And I would like to Insert the information from the top 3 tables into a new existing table named Table: "db.UsersWithRoles"    with  5 columns:  UserId, FirstName, Lastname, Email, RoleName     I have tried to use  Inner join but it is not really what i was looking for  because i want to add it to an existing table so i can read it with C# mvc and post it on my website I am not really sure how I am able to achive this.

satheesh d · Answer

This is the query to Join the three tables with the necessary fields.    SELECT USR.UserId,USR.FirstName,USR.Lastname,USR.Email,RO.RoleName FROM db.Users USR JOIN db.USERROLES URSRO ON URSRO.UserId = USR.UserId JOIN db.Roles RO ON RO.RolesId = URSRO.RolesId

Jignesh Kumar · Answer

Hi,   You can check using below query. If table is exist then will return result else it will not return   SELECT  *  FROM  sys.objects    WHERE  object_id = OBJECT_ID(N '[dbo].[ AspNetUsers ]' )  AND  type  in  (N 'U' )    for other table also you can check changing table name. If table are there then you can use your select statement.

osyris zosar · Answer

I am sorry i am new to SQL
Question: Am i able to acces a uncreated table by only using    Select  FirstName, LastName, Email, RoleId  from  AspNetUsers   Inner Join  AspNetUserRoles   ON  AspNetUsers.Id = AspNetUserRoles.UserId;    if so how do i acces that table since it is not physically created

Jignesh Kumar · Answer

Hi,

As per Table structure you already have user and role mapping then you no need new tble at all. When ever you need you get require user details and role based on your UserRoles table.

If you still want then use below two store procedure one for get require detail and one for insert in new table.

Create StoreProcedure to GetUserDetails and user role using below procedure. Call from your Mvc Method you will get your all five column then call save method to store in from you controller save method,

Procedure to Get detail for user GetUserAndRoleById

Create Procedure GetUserAndRoleById
@UserId int
AS
(
select usr.UserId, FirstName, Lastname, Email, RoleName
from Users usr
inner join UserRoles urole on usr.userId = urole.userId
inner join Roles rl on urole.RolesId = rl.RolesId
where usr.UserId= @UserId
)

Save Data Using below proceure

Create Procedure SaveUserRoleMapping
@UserId int,
@FirstName varchar(100),
@Lastname varchar(100),
@Email varchar(100),
@RoleName varchar(100)
AS
BEGIN
if Not Exists(select UserId from UsersWithRoles where UserId = @UserId and RoleName =@RoleName)
BEGIN
insert into UsersWithRoles (UserId, FirstName, Lastname, Email, RoleName)
values (@UserId, @FirstName, @Lastname, @Email, @RoleName)
END
ELSE
BEGIN
update UsersWithRoles set FirstName=@FirstName, Lastname = @Lastname, Email= @Email where UserId = @UserId and RoleName =@RoleName
END
END