sql server - Update and Insert When Condition is Matched in TSQL-Merge -

i have been trying write stored procedure can perform upsert using merge following condition

1. if record present change enddate of target yesterday's day i.e., present day - 1

2. if record not present insert new record

here table tblemployee used in sp

create table tblemployee (     [employeeid] [int] identity(1,1) not null,      [name] [varchar](10) not null,       [startdate] [date] not null,     [enddate] [date] not null ) 

here sp takes udtt input parameter

create procedure [dbo].[usp_upsertemployees] @typeemployee typeemployee readonly -- has same column tblemployye except employeeid begin     set nocount on;            merge tblemployee target     using @typeemployee source     on target.name = source.name       when matched , target.startdate < source.startdate                   --first update existing record enddate previous date shown below              update              set target.enddate = dateadd(day, -1, convert(date, source.startdate))              -- insert new record              --insert values(source.name, source.startdate, source.enddate);      when not matched target                  insert values(source.name, source.startdate, source.enddate);      set nocount off;         end 

how can perform both updating existing record , adding new record when column matched

can please explain me execution flow of merge in tsql i.e.,

    when matched --will execute everytime      when not matched target -- execute everytime      when not matched source -- execute everytime 

will above 3 condition executed everytime in merge or matching condition executed everytime

thanks in advance

this isn't merge meant (update , insert in same clause). accomplish this, can use output clause updated records only. merge/output combo picky. output updates target records got updated, have start target records in temp/table variable. match against source insert. won't allowed join output results directly source or use correlated subquery within where.

setup sample data

the code below sets sample data.

-- setup sample data declare @typeemployee table (     [name] [varchar](10) not null,       [startdate] [date] not null,     [enddate] [date] not null ) declare @tblemployee table (     [employeeid] [int] identity(1,1) not null,      [name] [varchar](10) not null,       [startdate] [date] not null,     [enddate] [date] not null    ) insert @tblemployee values ('emp a', '1/1/2016', '2/1/2016') insert @typeemployee values ('emp a', '1/5/2016', '2/2/2016'), ('emp b', '3/1/2016', '4/1/2016') 

updates stored procedure

you can use output @ end of merge have return modified records of target records, , including $action, whether insert, update, or delete.

however, result set merge / output cannot directly joined against source table can insert since target records back. can't use results of output within correlated sub-query source table either. easiest thing use temp table or table variable capture output.

-- logic upsert declare @updates table (     [name] [varchar](10) not null,       [startdate] [date] not null,     [enddate] [date] not null )  insert @updates     select         name,         startdate,         enddate     (         merge @tblemployee target         using @typeemployee source             on target.name = source.name          when matched , target.startdate < source.startdate                     --first update existing record enddate previous date shown below              update set                 enddate = dateadd(day, -1, convert(date, source.startdate))         when not matched target -- or matched , target.startdate >= source.startdate -- handle case?                     insert values(source.name, source.startdate, source.enddate)         output $action, inserted.name, inserted.startdate, inserted.enddate         -- use merge return changed records of target table     ) allchanges (actiontype, name, startdate, enddate)     allchanges.actiontype = 'update' -- records updated 

now you've captured output of merge , filtered updated target records, can outstanding insert filtering source records part of merge update.

insert @tblemployee     select         source.name,         source.startdate,         source.enddate     @typeemployee source     exists (         select *         @updates updates         updates.name = source.name             -- other join conditions ensure 1:1 match against source (start date?)     ) 

ouput

this output of sample records after change. intended target changes made.

-- show output select * @tblemployee