1
\$\begingroup\$

I have five groups in Group table as Group1, Group2, Group3, Group4, Group5 and whenever a new role added into Role table I have to make an entry into GROUP_ROLES table as below:

declare @gid1 int, @gid2 int,@gid3 int,@gid4 int,@gid5 int,@rid int
select @gid1 = id from Group where name = 'Group1'
select @gid2 = id from Group where name = 'Group2'
select @gid3 = id from Group where name = 'Group3'
select @gid4 = id from Group where name = 'Group4'
select @gid5 = id from Group where name = 'Group5'
select @rid = id from ROLE where name = 'newRole'

if @gid1 is not null and not exists(select * from GROUP_ROLES where group_id = @gid1 and roles_id = @rid)
begin
insert into GROUP_ROLES(group_id, roles_id) values (@gid1, @rid)
insert into GROUP_ROLES(group_id, roles_id) values (@gid2, @rid)
insert into GROUP_ROLES(group_id, roles_id) values (@gid3, @rid)
insert into GROUP_ROLES(group_id, roles_id) values (@gid4, @rid)
insert into GROUP_ROLES(group_id, roles_id) values (@gid5, @rid)
end

Right now I kinda have a redundant script. Is there a way to simplify the script?

\$\endgroup\$
1
\$\begingroup\$
if not exists
    (SELECT gr.*
     FROM Group.g INNER JOIN GROUP_ROLES gr ON g.id = gr.group_id INNER JOIN ROLE r ON gr.roles_id = r.id
     WHERE r.name = 'newRole' AND g.name IN ('Group1', 'Group2', 'Group3', 'Group4', 'Group5'))
begin
INSERT INTO GROUP_ROLES (group_id, roles_id)
    SELECT g.id AS group_id, r.id AS roles_id
    FROM Group g CROSS JOIN ROLE r
    WHERE r.name = 'newRole' AND g.name IN ('Group1', 'Group2', 'Group3', 'Group4', 'Group5')
end

Basically, you can INSERT the results of a SELECT query into the table. So first get the SELECT query right so that it returns the five rows that you want and then just add the INSERT clause at the beginning. Note that I gave the columns aliases that matched the destination columns, but that's actually unnecessary. I did it purely because I find having two id columns confusing.

SQL Server documentation of INSERT with a SELECT subquery.

According to INSERT INTO SELECT to avoid duplicates, you could also write

INSERT INTO GROUP_ROLES (group_id, roles_id)
    SELECT g.id AS group_id, r.id AS roles_id
    FROM Group g CROSS JOIN ROLE r
    WHERE r.name = 'newRole' AND g.name IN ('Group1', 'Group2', 'Group3', 'Group4', 'Group5')
      AND NOT EXISTS (SELECT gr2.*
         FROM Group.g2 INNER JOIN GROUP_ROLES gr2 ON g2.id = gr2.group_id INNER JOIN ROLE r2 ON gr2.roles_id = r2.id
         WHERE r2.name = 'newRole' AND g2.name IN ('Group1', 'Group2', 'Group3', 'Group4', 'Group5')
           AND gr.group_id = gr2.group_id AND gr.roles_id = gr2.roles_id)

I don't know that that's better though.

You can avoid the EXISTS checks entirely if you put a UNIQUE index on group_id and roles_id in the GROUP_ROLES table. Although you'll want to catch exceptions in that case.

For the obvious reasons, I haven't tested any of this. You can test the SELECT queries pretty easily, and it's not much harder to test the if not exists notation.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.