Programming stored procedures isn't my strongest point when it comes to programming, but I'm trying to get better.

I've stumbled upon a business requirement that's kind of hard for me and I was wondering if anyone had already dealt with it in their career.

There's this data in a staging table that needs to be loaded in a production table with insert for the new data and insert-update(backup like) for the old data.

The sample of data in production table looks like this:

AssetID BeginDate EndDate Field1 Field2 FieldN
1 (TS) (TS) 0.2351 1.3510 4.1234

Meanwhile in staging:

AssetID Field1 Field2 FieldN
1 0.2351 1.3510 4.1234

In prod the PK is AssetID and BeginDate meanwhile in staging is only AssetID (because the data is flushed every time).

The requirement is to write a stored procedure that when there are new assets in the staging area, they are inserted as new in prod(with BeginDate as CURRENT_TIMESTAMP and EndDate as NULL), meanwhile if there are data(in staging) that are different on the Field1,Field2 or FieldN fields from the production one, then a new record is written with the same AssetID but BeginDate as CURRENT_TIMESTAMP and EndDate as NULL, and the old record with the same combination (BeginDate memorized and EndDate is NULL) being updated with an EndDate as CURRENT_TIMESTAMP

Any of you have dealt with a similar task/requirement? If yes, could you share your solution please?

2 Answers 2


You can use a MERGE statement with an OUTPUT clause to achieve this. Basically, the MERGE statement compares each record in the staging table against the records in the data table. When a match is found, it updates the existing record to enter the End Date value, and if it doesn't find a match it inserts the record.

The OUTPUT clause is used to output the record when an UPDATE occurs, and the whole MERGE statement is wrapped inside an INSERT statement. This allows a new record to be inserted when a match is found since MERGE doesn't allow multiple statements.


  SELECT AssetID, BeginDate, EndDate, Value
    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()
      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'

Working example on db<>fiddle


You have to try to finalize any existent opened record for the given AssetID before insertion of the new record.

  DECLARE f1, f2, fN FLOAT; 

  SELECT Field1, Field2, FieldN
    FROM prod_table
   WHERE AssetID = _givenID
     AND EndDate = NULL
    INTO f1, f2, fN

  IF ( f1 <> _field1 OR 
       f2 <> _field2 OR 
       fN <> _fieldN )
      UPDATE prod_table    -- Here we suppose there is zero or one opened record
         SET EndDate = ts  -- If there is no records for _givenID, update do nothing
       WHERE AssetID = _givenID
         AND EndDate IS NULL  

      INSERT prod_table (AssetID, BeginDate, EndDate, Field1, Field2, FieldN)
      VALUES ( _givenID, ts, NULL, _field1, _field2, _fieldN )
  • There's no _givenID, it does apply on all the current records of staging and prod. So there's need to filter out the right records for new Assets and old Assets that have to be insert-updated only if the Field1,Field2 or FieldN in prod differs from staging.
    – Eugene
    Commented Oct 14, 2018 at 19:38
  • @Eugene You have stated that you want to do that by stored routines. SRs have parameters. I have a habit to mark the parameters names by leading underscore to differ them from column names. That is why _givenID appear. Any desired filtering can be added before update/insert. But I'm not sure you can do that all in one single statement. It is possible for single staged record at once and you can iterate your staging table by cursor and loop.
    – Kondybas
    Commented Oct 14, 2018 at 19:49
  • it's not required to be in a single statement, I was thinking about even using temporal tables like WITH ToInsert AS (SELECT ...), ToInsertAndUpdate AS (SELECT ...) as a way to filter the required records, but I'm not sure on the execution
    – Eugene
    Commented Oct 14, 2018 at 19:58
  • And there are no parameters, it just have the two tables and work on them in the stored procedure alone
    – Eugene
    Commented Oct 14, 2018 at 20:14
  • So you have to iterate the staging table by cursor/loop and do the same inside the loop. I'm not familiar with MS SQL-server enough to wright that all right now.
    – Kondybas
    Commented Oct 14, 2018 at 20:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.