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

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,我需要在组件表中创建新组件并使用新id插入到跟踪表中。此外,对于捐赠者表中不再存在的那些项,跟踪表“活动”列应标记为0.我可以在单个合并语句中实现此目的吗?

Donor Table

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

trace table

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

component table

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

OUTPUT TABLES AFTER MERGE:

合并后的输出表:

component table

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

trace table

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

1 个解决方案

#1

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

从理论上讲,应该有一个解决方案,在单一声明中这样做,但我到目前为止未能找到它。 *

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

以下是两个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 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:

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

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

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

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.

即使它显然是单列,并且常规的非合并更新工作正常。也许有人知道原因和/或解决方法。

#1