SQL Insert Into w / Subquery - check if does not exist

Question

Suppose there are two tables: UserRole (UserRoleID, UserID, RoleID) and UserPermission (UserPermissionID, UserID, PermissionID, RegionID).

I have the following INSERT w / subquery to create new rows in UserPermission for each UserID with a specific RoleID:

INSERT INTO UserPermission (UserPermissionID, UserID, PermissionID, RegionID)
SELECT NEWID(), UserID, @PermID, NULL
FROM UserRole WHERE RoleID = '<specific guid>'

How to check if rows that I insert exist in the database? I know how to check a single record (IF IT DOESN’T EXIST (blah blah)), but what would be the best way to check if there is a combination of a specific UserID and PermissionID (there will always be @PermID - a specific GUID) in a row in the UserPermission table?

Answer 1

:

INSERT INTO UserPermission (UserPermissionID, UserID, PermissionID, RegionID)
SELECT NEWID(), UserID, @PermID, NULL
FROM UserRole r
WHERE RoleID = '<specific guid>'
AND NOT EXISTS (SELECT * FROM UserPermission WHERE UserID = r.UserID AND @PERMID = PermissionID);