SQL Insert Query

Posted on 2014-08-15
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 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
Question by:KANEDA 0149
    LVL 25

    Accepted Solution

    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)

    Author Comment

    by:KANEDA 0149
    Thanks Shaun for the quick response, let me test that out!

    Author Comment

    by:KANEDA 0149
    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!
    LVL 25

    Expert Comment

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

    Author Closing Comment

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

    Author Comment

    by:KANEDA 0149
    Thanks for bringing it to my attention!
    LVL 25

    Expert Comment

    by:Shaun Kline
    It took. Thanks.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now