I am trying to merge two tables with n periods into one:

I have the below tables:

Tables

Period1 .. Period750 represents a date, eg, period 1 = Jan 1st, Period2, Jan 2nd ...

How can we get to that result ?

thank you for the advice, regards, Oscar

CodePudding user response：

select
   product, startdate
  , sum(period1) as period1
  , sum(period2) as period2
  ...
  , sum(period750) as period750
from(
   select * from table1 union all
   select * from table2 union all
  ...
)
group by product, startdate

CodePudding user response：

Use a MERGE statement:

MERGE INTO table1 t1
USING table2 t2
ON (t1.product = t2.product AND t1.stardate = t2.stardate)
WHEN MATCHED THEN
  UPDATE
  SET period1 = t1.period1   t2.period1,
      period2 = t1.period2   t2.period2,
      -- ...
      period749 = t1.period749   t2.period749,
      period750 = t1.period740   t2.period750
WHEN NOT MATCHED THEN
  INSERT (
    product,
    stardate,
    period1,
    period2,
    -- ...,
    period749,
    period750
  ) VALUES (
    t2.product,
    t2.stardate,
    t2.period1,
    t2.period2,
    -- ...,
    t2.period749,
    t2.period750
  );