将数据合并到两个目标表中

Question

我需要将捐赠者表中的数据合并到两个目标表中。结构如下所示。如果在跟踪表中找不到projid，则需要在组件表中创建新组件，并使用新ID插入到跟踪表中。此外，对于那些在捐赠者表中不存在的项目，跟踪表的“active”列应标记为0。我能否在单个合并语句中实现这一点？ 捐赠者表

projid      | datestamp    | Ownerid
-------------------------------------------------
c_abc        1-jan-2013      name1
c_def        2-jan-2013      name3
c_ghi        3-jan-2013      name4

追踪表

compid      |projid     |active | ... 
-----------------------------------------------
123           c_abc      1
124           c_xyz      1
125           c_def      1

组件表

compid      |ownerid
-------------------------
123      name1
124      name2
125      name3

合并后的输出表格:

组件表格

compid      |ownerid
-------------------------
123      name1
124      name2
125      name3
126      name4

跟踪表

compid      |projid     |active | ... 
-----------------------------------------------
123          c_abc       1
124          c_xyz       0
125          c_def       1
126          c_ghi       1

- mhn

---

那个 name4 是从哪里来的？ - Nenad Zivkovic

name4也来自于donor表。 - mhn

@mhn - 你是说在Donor表中还有另一列包含了那个文本吗？如果是的话，能否将其添加到你的示例中？ - Damien_The_Unbeliever

是的，有的。现在已经编辑过了。 - mhn

Answer 1

理论上来说，应该有一种方法可以用单个语句来完成这个任务，但是到目前为止我还没有找到。*

下面是使用两个MERGE语句的方法：

WITH CTE_trgt AS 
(
    SELECT c.compid, c.ownerid, t.projid, t.active 
    FROM component c
    INNER JOIN trace t ON c.compid = t.compid
)
MERGE CTE_trgt t
USING Donor s
ON t.projid = s.projid
WHEN NOT MATCHED BY TARGET
    THEN INSERT (ownerid)
    VALUES (s.ownerid)
OUTPUT
    INSERTED.compid, s.projid, 1 INTO trace;


MERGE trace t
USING Donor s
ON t.projid = s.projid
WHEN NOT MATCHED BY SOURCE 
    THEN UPDATE SET t.active = 0;

SQLFiddle演示

---

更新Active列的部分：

WHEN NOT MATCHED BY SOURCE 
    THEN UPDATE SET t.active = 0

应该能够适应上述查询，为所有操作创建单个合并语句，但会出现错误：

视图或函数“t”不可更新，因为修改会影响多个基表

即使很明显只有单列，常规非合并更新也可以正常工作。也许有人知道原因和/或解决方法。

- Nenad Zivkovic

---

嗨Nenad，非常感谢您的输入！第一个合并语句很好。但是更新跟踪表中活动标志的语句我无法按照自己想要的方式进行自定义。我错过了跟踪中的另一列donorid。因此，我只需要更新那些donorid ='MyDonorId'的行。我无法在合并语句的任何地方容纳这个where子句。我尝试过。 - mhn

...MERGE（从trace表中选择donorid = 'MyDonorId'的所有记录）t USING Donor s ON t.projid = s.projid WHEN NOT MATCHED BY SOURCE THEN UPDATE SET t.active = 0; 这里语法有误 :( - mhn

@mhn 你可以使用通用表达式 - WITH CTE 语法，就像第一个语句中一样，来过滤你的目标。 - Nenad Zivkovic

WITH CTE_Trace as (select * from trace where donorid = 'MyDonorId' ) MERGE CTE_Trace t USING Donor... - Nenad Zivkovic