SQL Server 2014

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Data
(
  AssetID INT,
  BeginDate DATETIME,
  EndDate DATETIME NULL,
  Value DECIMAL(10,2)
)

CREATE TABLE Stage
(
  AssetID INT,
  Value DECIMAL(10,2)
)
CREATE PROCEDURE spLoadData
AS
BEGIN
  
  INSERT INTO Data
  SELECT AssetID, BeginDate, EndDate, Value
  FROM
  (
    MERGE INTO Data as target
    USING Stage as source ON (source.AssetID = target.AssetID)
    WHEN MATCHED AND target.Value <> source.Value THEN
      UPDATE SET EndDate = GETDATE()
    WHEN NOT MATCHED THEN
      INSERT (AssetID, BeginDate, EndDate, Value)
      VALUES (source.AssetID, GETDATE(), NULL, source.Value)
    OUTPUT $action AS Action, Source.AssetID AS AssetID, GETDATE() AS BeginDate, NULL AS EndDate, source.Value AS Value
  )
  AS Changes (Action, AssetID, BeginDate, EndDate, Value) WHERE Action = 'UPDATE'

END
INSERT INTO Stage (AssetID, Value)
VALUES (1, 1.5), (2, 1.5)
2 rows affected

-- Two records are inserted. End Date is NULL because these are new records

EXEC spLoadData

SELECT * FROM Data


AssetID
BeginDate
EndDate
Value


1
2022-05-27 09:27:24.237
null
1.50

2
2022-05-27 09:27:24.237
null
1.50


TRUNCATE TABLE Stage;

INSERT INTO Stage (AssetID, Value)
VALUES (1, 3.5)
  , (2, 17.5)
  , (3, 1.8)
3 rows affected

-- Two records are updated to fill in the End Date. 
-- Two more records are inserted with the same Asset ID as the records that were updated
-- One brand new Asset ID record is inserted.

EXEC spLoadData

SELECT * FROM Data


AssetID
BeginDate
EndDate
Value


1
2022-05-27 09:27:24.237
2022-05-27 09:27:24.267
1.50

2
2022-05-27 09:27:24.237
2022-05-27 09:27:24.267
1.50

3
2022-05-27 09:27:24.267
null
1.80

1
2022-05-27 09:27:24.267
null
3.50

2
2022-05-27 09:27:24.267
null
17.50

AssetID	BeginDate	EndDate	Value
1	2022-05-27 09:27:24.237	null	1.50
2	2022-05-27 09:27:24.237	null	1.50

AssetID	BeginDate	EndDate	Value
1	2022-05-27 09:27:24.237	2022-05-27 09:27:24.267	1.50
2	2022-05-27 09:27:24.237	2022-05-27 09:27:24.267	1.50
3	2022-05-27 09:27:24.267	null	1.80
1	2022-05-27 09:27:24.267	null	3.50
2	2022-05-27 09:27:24.267	null	17.50