MERGE statement to update temporal table (SCD 2)

Transact-SQL
,
1

I want to merge current inventory data into a temporal (SCD 2) table. I've worked it out, but my only issue now is that once there is a match, it should update (age out) the existing row AND also insert a new row of data.

How would I do this? I can't figure out the syntax to run both & it doesn't seem to accept stored procedures.

MERGE [MyDB].[dbo].[TemporalInventory] AS Target
USING [OtherDB].[dbo].[bvc_Product] AS Source
    ON Target.[ProductID] = Source.[ID]
WHEN MATCHED
    AND (Target.[AggregatePhysicalQty] <> Source.[AggregatePhysicalQty]
    	    AND [DateExpired] > GETDATE() )
THEN
   UPDATE SET
      Target.[DateExpired] = GETDATE()
--   INSERT ([ProductID],[AggregatePhysicalQty],[DateAdded],[DateUpdated],[DateExpired])
--   VALUES (Source.ID, Source.[AggregatePhysicalQty], GETDATE(), GETDATE(), '5000-01-01 00:00:00')

WHEN NOT MATCHED BY TARGET
    AND (Source.ClientID='100'
	    AND Source.Status ='1'
		AND LEN(Source.ShadowOf) = 0
	    AND Source.IsBundle = 0)
THEN
   INSERT ([ProductID],[AggregatePhysicalQty],[DateAdded],[DateUpdated],[DateExpired])
   VALUES (Source.ID, Source.[AggregatePhysicalQty], GETDATE(), GETDATE(), '5000-01-01 00:00:00')
WHEN NOT MATCHED BY SOURCE THEN DELETE;
2

I'd add an OUTPUT clause then use the MERGE statement in a composed dml:

INSERT INTO...
SELECT ...
FROM 
( 
    MERGE ...
    OUTPUT ...
)
3

Here's a full example:

DECLARE @InsertedOrders AS TABLE
(
  orderid   INT  NOT NULL PRIMARY KEY,
  custid    INT  NOT NULL,
  empid     INT  NOT NULL,
  orderdate DATE NOT NULL
);

INSERT INTO @InsertedOrders(orderid, custid, empid, orderdate)
  SELECT orderid, custid, empid, orderdate
  FROM (MERGE INTO Sales.MyOrders AS TGT
        USING (VALUES(1,        70,      1,      '20061218'),
                     (2,        70,      7,      '20070429'),
                     (3,        70,      7,      '20070820'),
                     (4,        70,      3,      '20080114'),
                     (5,        70,      1,      '20080226'),
                     (6,        70,      2,      '20080410'))
               AS SRC(orderid,  custid,  empid,  orderdate )
          ON SRC.orderid= TGT.orderid
        WHEN MATCHED AND (   TGT.custid    <> SRC.custid
                          OR TGT.empid     <> SRC.empid
                          OR TGT.orderdate <> SRC.orderdate) THEN UPDATE
          SET TGT.custid    = SRC.custid,
              TGT.empid     = SRC.empid,
              TGT.orderdate = SRC.orderdate
        WHEN NOT MATCHED THEN INSERT
          VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate)
        WHEN NOT MATCHED BY SOURCE THEN
          DELETE
        OUTPUT
          $action AS the_action, inserted.*) AS D
  WHERE the_action = 'INSERT';

SELECT *
FROM @InsertedOrders;