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
NULL), meanwhile if there are data(in staging) that are different on the
FieldN fields from the production one, then a new record is written with the same
NULL, and the old record with the same combination (
BeginDate memorized and
NULL) being updated with an
Any of you have dealed with a similar task/requirement? If yes, could you share your solution please?
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