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
KANEDA 0149Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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!
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.