SQL Server MERGE + Joining other tables

I am using the MERGE statement within a database project to populate reference data from a static value set, such as the following below: ``` MERGE INTO dbo.[User] AS TARGET USING (VALUES ('me@somewhere.com', 'My Name')) AS SOURCE(UserName, FullName) ON SOURCE.UserName = TARGET.UserName WHEN NOT MATCHED BY TARGET THEN INSERT (UserId, UserName, FullName) VALUES (NEWID(), UserName, FullName); ``` The problem comes in when I want to populate the secondary table based of content in other tables. For example, my UserPermission table contains user ID and role ID and I'd like my static value set to be something like ('me@somewhere.com', 'Admin') and be able to join to User and Permission to get the ID values for INSERTing. Not sure where do to that... Edit: User Table(ID, Username) 1, John Smith 2, Mark Wahlerg Role Table(ID, RoleName) 1, Administrator 2, User 3, Guest User-Role Table (User ID, Role ID) I want the SQL for the MERGE statement to adjust the User-Role table such that I can do specify something like: ``` USING(VALUES ('John Smith', 'Administrator'), ('Mark Wahlburg', 'User') ``` and it will join to determine the IDs, insert the combinations that dont exists (and maybe delete the ones that do, but aren't in the MERGE. Solution: ``` WITH CTE AS ( SELECT UserId, RoleId FROM (VALUES ('John Smith', 'Administrator'), ('Mark Wahlburg', 'User')) AS SOURCE(UserName, RoleName) INNER JOIN User ON SOURCE.UserName = User.UserName INNER JOIN Role ON SOURCE.RoleName = Role.RoleName ) MERGE INTO UserRole AS TARGET USING CTE ON CTE.UserId = TARGET.UserID AND CTE.RoleId = TARGET.UserId WHEN NOT MATCHED BY TARGET THEN INSERT(UserId, RoleId) VALUES(UserId, RoleId) ```