Estoy utilizando la declaración MERGE dentro de un proyecto de base de datos para completar los datos de referencia de un conjunto de valores estáticos, como los siguientes a continuación:

<code>    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);
</code>

El problema surge cuando quiero rellenar la tabla secundaria basada en el contenido de otras tablas. Por ejemplo, mi tabla UserPermission contiene la ID de usuario y la ID de rol y me gustaría que mi conjunto de valores estáticos fuera algo así como ('[email protected]', 'Admin') y poder unirme a Usuario y Permiso para obtener el Valores de ID para INSERTAR. No estoy seguro de dónde hacer eso ...

Editar:

Tabla de usuario (ID, nombre de usuario) 1, John Smith 2, Mark Wahlerg

Tabla de roles (ID, RoleName) 1, Administrator 2, User 3, Guest

Tabla de roles de usuario (ID de usuario, ID de rol)

Quiero que el SQL para la sentencia MERGE ajuste la tabla de roles de usuario de manera que pueda especificar algo como:

<code>USING(VALUES
 ('John Smith', 'Administrator'),
 ('Mark Wahlburg', 'User')
</code>

y se unirá para determinar los ID, inserte las combinaciones que no existen (y tal vez elimine las que sí existen, pero no están en el MERGE).

Solución:

<code>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)
</code>