How to update/insert rows without merge/upsert

1

Hi,

I have tmpCompensation that contains some data imported from some source. The file contains information including customernumber and deliverydate. If table Compensation contains the same customernumber with same deliverydate then I just need to update the row. Otherwise, insert a new row. The problem is that for customer 7865343 the row is updated but also a new row is added as it compares also the date 2017-03-04 with the date in the tmp table and since the date is different it also adds a new row with the date 2020-12-10. This shouldn't happen as 2020-12-10 for that customer exists in Compensation table so it should only update the row in Compensation. How can I achieve this?

The resultset after the update/insert should be as follows in the
 compensation table
7865343, 2020-12-10,'bike','087543'  
 7865343, 2017-03-04,'home','864380'  
95345, 2018-12-10,'home','864380'
95345, 2019-12-10,'home','864383'
Thanks


create table tmpCompensation (customerNumber int, deliveryDate varchar (20), deliveryMethod varchar(50), recid bigint)
  create table Compensation (customerNumber int, deliveryDate varchar (20), deliveryMethod varchar(50), recid bigint)

  insert into tmpCompensation  select 434353, 2020-10-10,'bike','76756454' union
select 7865343, 2020-12-10,'bike','087543' union
select 95345, 2019-12-10,'home','864380' 

insert into Compensation  select 434353, 2020-10-10,'bike','76756454' union
select 7865343, 2020-12-10,'car','087543' union
select 7865343, 2017-03-04,'home','864380' union
select 95345, 2018-12-10,'home','864383'
2

customernumber exists update
customernumber does not exist insert

leave the datepart

3

How do you expect to be able to UPDATE or INSERT rows without using UPDATE and then INSERT ("without merge/upsert")? That's literally not possible.

4

so what happened to 434353 in final result?

--i like to use table variable for sample so as not to deal with drop etc
declare @tmpCompensation table(customerNumber int, deliveryDate date, 
deliveryMethod varchar(50), recid bigint)
declare @Compensation table(customerNumber int, deliveryDate date, 
deliveryMethod varchar(50), recid bigint)

insert into @tmpCompensation  
select 434353, '2020-10-10','bike','76756454' union
select 7865343, '2020-12-10','bike','087543' union
select 95345, '2019-12-10','home','864380' 


insert into @Compensation(customerNumber,deliveryDate,deliveryMethod,
recid)  
select 434353, '2020-10-10','bike','76756454' union
select 7865343, '2020-12-10','car','087543' union
select 7865343, '2017-03-04','home','864380' union
select 95345, '2018-12-10','home','864383' 

update tgt
    set tgt.deliveryMethod = src.deliveryMethod,
	    tgt.recid = src.recid
  from @Compensation tgt 
  join @tmpCompensation src 
	on src.customerNumber = tgt.customerNumber
   and src.deliveryDate = tgt.deliveryDate

select 'after update', * from @Compensation	

insert into @Compensation
select *
  from @tmpCompensation src
  where not exists(select 1 
                     from @Compensation tgt 
					where src.customerNumber = tgt.customerNumber
					  and src.deliveryDate = tgt.deliveryDate
					)
select 'after insert', * from @Compensation