将数据合并到两个目标表中 [英] Merge data into two destination tables

问题描述

我需要将供体表中的数据合并到两个目标表中.结构如下.如果在跟踪表中找不到projid，则需要在组件表中创建新组件，并使用新的ID插入到跟踪表中.另外，对于捐赠者表中不再存在的那些项目，跟踪表的活动"列应标记为0.我可以在单个merge语句中实现这一点吗?

I need to merge data from a donor table into two destination tables. The structure is as below. If a projid is not found in the trace table, i need to create new component in the component table and use the new id to insert into the trace table. Also, for those items that no longer exist in the donor table, the trace table 'active' column should be marked 0. Can i achieve this in a single merge statement?

捐款表

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

推荐答案

从理论上讲，应该有一个解决方案可以在单个语句中执行此操作，但是到目前为止，我仍然找不到它. *

Theoretically, there should be a solution to do this in single statement, but I have so far failed to find it. *

这是使用两个MERGE语句完成的方法:

Here is how it can be done with two MERGE statements:

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演示

SQLFiddle DEMO

* 具有更新活动列的部分:

* Part with updating Active column:

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

应该能够适合上层查询，为所有对象创建一个单个合并语句操作，但会引发错误:

should be able to fit in the upper query creating a single merge statement for all operations, but it throws an error:

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

View or function 't' is not updatable because the modification affects multiple base tables

即使它显然是单列，并且定期非合并更新也可以正常工作.也许有人知道原因和/或解决方法.

even if it's obviously single column, and regular non-merge update works fine. Maybe someone knows a reason and/or a workaround for this.

这篇关于将数据合并到两个目标表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！