sql-server - SQL Server MERGE + 连接其他表

Question

我在数据库项目中使用 MERGE 语句从静态值集中填充引用数据，如下所示:

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

当我想根据其他表中的内容填充辅助表时，问题就出现了。例如，我的 UserPermission 表包含用户 ID 和角色 ID，我希望我的静态值设置为类似 ('me@somewhere.com', 'Admin') 并能够加入用户和权限以获得用于插入的 ID 值。不知道在哪里做...

编辑:

用户表(ID、用户名)1、约翰·史密斯2、马克·沃勒格

角色表(ID、角色名称)1、管理员2、用户3、嘉宾

用户角色表(用户 ID、角色 ID)

我希望 MERGE 语句的 SQL 调整用户角色表，以便我可以指定如下内容:

USING(VALUES
 ('John Smith', 'Administrator'),
 ('Mark Wahlburg', 'User')

它将连接以确定 ID，插入不存在的组合(并且可能删除存在但不在 MERGE 中的组合。

解决方案:

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 支持 CTE，因此也许您可以使用它作为源，组合静态数据并在 cte 内执行任何联接。