SQL Server MERGE +连接其他表 [英] SQL Server MERGE + Joining other tables

问题描述

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

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
    ('[e-mail]', '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，我希望将我的静态值设置为('[e-mail]'，'Admin')，并能够加入User和Permission以获取INSERTing的ID值.不确定该在哪里做...

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 ('[e-mail]', 'Admin') and be able to join to User and Permission to get the ID values for INSERTing. Not sure where do to that...

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

User Table(ID, Username) 1, John Smith 2, Mark Wahlerg

角色表(ID，RoleName) 1，管理员 2，用户 3，客人

Role Table(ID, RoleName) 1, Administrator 2, User 3, Guest

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

User-Role Table (User ID, Role ID)

我希望MERGE语句的SQL调整User-Role表，以便可以执行以下操作:

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

它将合并以确定ID，插入不存在的组合(并可能删除存在但不包含在MERGE中的组合.

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.

解决方案:

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)

推荐答案

合并支持CTE，因此也许您可以将其用作源，合并静态数据并在cte内执行任何联接.

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

这篇关于SQL Server MERGE +连接其他表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！