70-433 SQL Server MERGE

The MERGE statement is best illustrated by a simple example, suppose you need to periodically update the SALES_HISTORY table with data from the main SALES table:

MERGE sale_history as sh USING SALES s ON (sh.empID = s.empID)

WHEN MATCHED THEN UPDATE sale_history SET

  TotalSales = s.TotalSales

  LastUpdate = GETDATE()

  Dept = s.Dept

WHEN NOT MATCHED THEN INSERT VALUES

  (s.TotalSales, GETDATE(), s.Dept)

http://stackoverflow.com/questions/1271564/non-trivial-merge-of-two-tables

You don't say whether you want to merge into one of the existing tables or into a new table. But either way it is not "non-trivial".

Into one of the existing tables, use MERGE (the clue is in the question).

SQL> select * from t1;

        ID TS             MONEY
---------- --------- ----------
         1 25-JUL-09        123
         2 04-AUG-09         67

SQL> select * from t2;

        ID TS             MONEY
---------- --------- ----------
         2 08-AUG-09         67
         3 10-AUG-09        787

SQL> merge into t1
  2     using   t2
  3     on ( t1.id = t2.id )
  4     when matched then
  5          update set ts = ts + ((t2.ts - t1.ts) / 2)
  6     when not matched then
  7          insert
  8             (id, ts, money)
  9     values
 10              (t2.id, t2.ts, t2.money)
 11  /

2 rows merged.

SQL> select * from t1
  2  /

        ID TS             MONEY
---------- --------- ----------
         1 25-JUL-09        123
         2 10-AUG-09         67
         3 10-AUG-09        787

SQL>

If you want to insert both sets of data into a new table then you can do it like this:

SQL> insert all
  2      when t1_id = t2_id then
  3          into t3 values (t1_id, t1_ts + ((t2_ts - t1_ts)/2), t1_money)
  4      when t1_id is not null and t2_id is null then
  5          into t3 values (t1_id, t1_ts, t1_money)
  6      when t1_id is null and t2_id is not null then
  7          into t3 values (t2_id, t2_ts, t2_money)
  8  select t1.id as t1_id
  9         , t1.ts as t1_ts
 10         , t1.money as t1_money
 11         , t2.id as t2_id
 12         , t2.ts as t2_ts
 13         , t2.money as t2_money
 14  from t1 full outer join t2 on t1.id = t2.id
 15  /
SQL> select * from t3
  2  /

        ID TS             MONEY
---------- --------- ----------
         2 06-AUG-09         67
         1 25-JUL-09        123
         3 10-AUG-09        787

SQL>