We help IT Professionals succeed at work.

SQL Insert Query

221 Views
Last Modified: 2014-08-18
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 QUERY
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


SELECT QUERY OUTPUT
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
Comment
Watch Question

Lead Software Engineer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks Shaun for the quick response, let me test that out!

Author

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!
Shaun KlineLead Software Engineer
CERTIFIED EXPERT

Commented:
The closing comment was that the solution provided worked perfectly, but the question is being closed using the comment entered by the author.

Author

Commented:
My apologies Shaun, I thought I selected the accept solution and selected A grade. Did it take this time?

Author

Commented:
Thanks for bringing it to my attention!
Shaun KlineLead Software Engineer
CERTIFIED EXPERT

Commented:
It took. Thanks.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.