Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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
like image 840
mhn Avatar asked Aug 09 '13 08:08

mhn


People also ask

How can I MERGE two table data?

You can merge (combine) rows from one table into another simply by pasting the data in the first empty cells below the target table. The table will increase in size to include the new rows.

How do I MERGE two tables in SQL MERGE?

Merging tables by columns. Multiple tables can be merged by columns in SQL using joins. Joins merge two tables based on the specified columns (generally, the primary key of one table and a foreign key of the other).

Can we insert data into two tables simultaneously?

The T-SQL function OUTPUT, which was introduced in 2005, can be used to insert multiple values into multiple tables in a single statement. The output values of each row that was part of an INSERT, UPDATE or DELETE operation are returned by the OUTPUT clause.

What is database MERGE?

Data merging is the process of combining two or more similar records into a single one. Merging is done to add variables to a dataset, append or add cases or observations to a dataset, or remove duplicates and other incorrect information.


1 Answers

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.

like image 124
Nenad Zivkovic Avatar answered Oct 09 '22 19:10

Nenad Zivkovic