• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 174
  • Last Modified:

SQL Insert Query

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)

Open in new window

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
  • 4
  • 3
1 Solution
Shaun KlineLead Software EngineerCommented:
If table "APX.ObjectRoleAccess" is a join table between reports and roles, you should be able to select the report IDs from our report table and insert them with the role ID into that table. Something like:

insert into APX.ObjectRoleAccess
(ReportID, RoleID)
select reportID, '113903'
from dbo.AdvReport
where ReportID in (89152,112356,18799)
KANEDA 0149Author Commented:
Thanks Shaun for the quick response, let me test that out!
KANEDA 0149Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for KANEDA 0149's comment #a40263401

for the following reason:

Worked perfectly Shaun!
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Shaun KlineLead Software EngineerCommented:
The closing comment was that the solution provided worked perfectly, but the question is being closed using the comment entered by the author.
KANEDA 0149Author Commented:
My apologies Shaun, I thought I selected the accept solution and selected A grade. Did it take this time?
KANEDA 0149Author Commented:
Thanks for bringing it to my attention!
Shaun KlineLead Software EngineerCommented:
It took. Thanks.

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now