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?