0

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 dealed 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 dealed with a similar task/requirement? If yes, could you share your solution please?

0

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

BEGIN
  DECLARE ts TIMESTAMP = NOW();
  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 )
    THEN 
      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 )
      ;
  END IF;
END
  • 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 Oct 14 '18 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 Oct 14 '18 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 Oct 14 '18 at 19:58
  • And there are no parameters, it just have the two tables and work on them in the stored procedure alone – Eugene Oct 14 '18 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 Oct 14 '18 at 20:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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