I have a table which has kind of below structure.
enrollment product startdate enddate
1 A1 1/1/2016 1/6/2016
1 A1 1/9/2016 1/15/2016
1 A1 1/19/2016 null
Once data is inserted, enrollment, product and startdate would never change.
Only the end date will change (from NULL to somedate)
Once enddate is set, it will never change to NULL (the actual date can be changed)
There are 2 processes that updates the enddate
ETL (nightly) and an API call(anytime)
ETL never gets the enddate in the file, so it sets it as getdate()
But API always gets the enddate.
So there is a possibility that above data can be changed to below, when called from API
enrollment product startdate enddate
1 A1 1/1/2016 1/5/2016
1 A1 1/9/2016 1/13/2016
1 A1 1/19/2016 null
We are using 2005, so I cannot use merge(else I was thinking of using the merge joining on enrollment, product and startdate).
Is there any efficient way of doing this, instead of row by row check - Insert or Update?
eg:
in database, I have
enrollment product startdate enddate
1 A1 1/1/2016 1/6/2016
1 A1 1/9/2016 1/15/2016
1 A1 1/19/2016 null
from API I get
enrollment product startdate enddate
1 A1 1/1/2016 1/5/2016
1 A1 1/9/2016 1/13/2016
1 A1 1/19/2016 null
and I need to update the end dates accordingly.