CODEWITHSUNDEEP
sqlsql-serversql-server-2008merge
mhn
mhn

Reputation: 2750

Merge data into two destination tables

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?

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

Upvotes: 10

Views: 17775

Answers (1)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

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:

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

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.

Upvotes: 7

Related Questions