SQLTeam.com | Weblogs | Forums

Merge like in 2005


#1

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.


#2

Typically:

Update the rows that already exist
Insert new rows.