Inbound table:

There are some more measure fields in this table. In each row is only one measure field <> NULL.

I want to transfer/merge this table into another one, which is empty at the beginning:

Outbound table

I've tried that with merge:

MERGE @OutboundTable AS TARGET
USING @InboundTable AS SOURCE
        ON  TARGET.[Date]   = SOURCE.[Date] 
        AND TARGET.[P_ID]   = SOURCE.[P_ID]
        AND TARGET.[S_ID]   = SOURCE.[S_ID]
WHEN MATCHED THEN UPDATE SET 
         TARGET.[Measure1] = ISNULL(SOURCE.[Measure1],TARGET.[Measure1]),
         TARGET.[Measure2] = ISNULL(SOURCE.[Measure2],TARGET.[Measure2])
    WHEN NOT MATCHED THEN
    INSERT
    (
          [Date]
        , [P_D]
        , [S_ID]
        , [Measure1]
        , [Measure2]
    )
    VALUES
    (
          SOURCE.[Date]
        , SOURCE.[P_ID]
        , SOURCE.[S_ID]
        , SOURCE.[Measure1]
        , SOURCE.[Measure2]
    )
    ;

But it did not work. The outbound table contained the same rows as the inbound table.

How can I perform this transformation?

CodePudding user response：

Query:

INSERT INTO outbound (date, P_ID, S_ID, Measure1, Measure2)
SELECT date, P_ID, S_ID, MAX(Measure1) AS Measure1, MAX(Measure2) AS Measure2
  FROM inbound_table
  GROUP BY date, P_ID, S_ID;