SQL Server MERGE + Joining other tables

Question

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
    ('[email protected]', '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 ('[email protected]', '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)

Answer 1

Merge supports CTEs so perhaps you can use this as your source, combining your static data and performing any joins within the cte.