troubleshooting Question

SQL Insert Query

Avatar of KANEDA 0149
KANEDA 0149Flag for United States of America asked on
Microsoft SQL Server 2008SQL
7 Comments1 Solution271 ViewsLast Modified:
Hi, I'm drawing a blank and I am hoping someone can help me out real quick.  I have a query below which will return a list of report ID, report name and role ID's associated.  I need create an INSERT statement where based on multiple specific report IDs, I need to add a new role ID to this group.

I need to add role ID 113903 to report ID's 89152, 112356 and 18799.  Can someone quickly help please!

select distinct r.ReportID, r.ReportFileName, o.DisplayName, ra.RoleID, role.RoleName
from dbo.AdvReport r
join dbo.AoObject o on o.ObjectID = r.ReportID
join vUserReportsAccess ur on r.ReportID = ur.ObjectID
join dbo.AoRolePermission rp on r.ClassID = rp.ClassID
join APX.ObjectRoleAccess ra on r.ReportID = ra.ObjectID
--join dbo.vAoUserRoleAccess ura on ur.UserID = ura.UserID
join AdvApp.vRole role on ra.RoleID = role.RoleID
where o.IsAccessibleToAllRoles = 0
and r.ReportID in (89152,112356,18799)

ReportID      ReportFileName      DisplayName      RoleID
89152      u47807.rep      13F Custom      17269
89152      u47807.rep      13F Custom      113895

NEED TO INSERT role ID 113903 which when I re-run the SELECT QUERY below is the NEW OUTPUT
ReportID      ReportFileName      DisplayName      RoleID      RoleName
89152      u47807.rep      13F Custom      17269      CPR
89152      u47807.rep      13F Custom      113895      CPR Lite
89152      u47807.rep      13F Custom      113903      Operations Lite
Shaun Kline
Lead Software Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros