Update and Insert When Condition is Matched in TSQL-Merge

Question

I have been trying to Write a Stored Procedure where i can perform UpSert using Merge with the Following Condition

1. If Record is Present then change EndDate of Target to Yesterday's day i.e., Present Day - 1

2. If Record is not Present then Insert New Record

Here is the Table tblEmployee i 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 is my SP which Takes UDTT as Input parameter

CREATE PROCEDURE [dbo].[usp_UpsertEmployees]
@typeEmployee typeEmployee READONLY -- It has same column like tblEmployye except EmployeeID
AS
BEGIN
    SET NOCOUNT ON;      

    MERGE INTO tblEmployee AS TARGET
    USING @typeEmployee AS SOURCE
    ON TARGET.Name = SOURCE.Name 

    WHEN MATCHED and TARGET.StartDate < SOURCE.StartDate
    THEN 

            --First Update Existing Record EndDate to Previous Date as shown below 
            UPDATE 
            set TARGET.EndDate = DATEADD(day, -1, convert(date, SOURCE.StartDate))

            -- Now Insert New Record 
            --INSERT VALUES(SOURCE.Name, SOURCE.StartDate, SOURCE.EndDate);

    WHEN NOT MATCHED by TARGET 
    THEN
            INSERT VALUES(SOURCE.Name, SOURCE.StartDate, SOURCE.EndDate);

    SET NOCOUNT OFF;        
END

How can i perform both Updating Existing Record and Adding New Record When Column is matched

Can Please someone Explain me the Execution Flow of Merge in TSQL i.e.,

    WHEN MATCHED --Will this Execute Everytime

    WHEN NOT MATCHED by TARGET -- Will this Execute Everytime

    WHEN NOT MATCHED by SOURCE -- Will this Execute Everytime

Will all above 3 condition get executed everytime in Merge or only Matching condition is executed Everytime

Thanks in Advance

Branchiopod

when matched then begin /* update and insert here */ end... – Clamatorial

Getting Compilation Error "Incorrect Syntax near 'Begin'. Expecting DELETE, INSERT, or UPDATE. – Branchiopod

well, I guess I was wrong... – Clamatorial

Answer 1

This isn't what MERGE is meant to do (update and insert in same clause). To accomplish this, you can use the OUTPUT clause to get all the updated records only. The MERGE/OUTPUT combo is very picky. Your OUTPUT updates are really the TARGET records that got updated, so you have to start the TARGET records in a temp/table variable. Then you match those back against the SOURCE to do the INSERT. You won't be allowed to join the output results directly back to source or even use as a correlated subquery within the WHERE.

Setup some sample data

The code below just sets up some 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 to Stored Procedure

You can use OUTPUT at the end of a MERGE to have it return the modified records of the target records, and by including $action, you will also get whether it was an insert, update, or delete.

However, the result set from MERGE / OUTPUT cannot be directly joined against the SOURCE table so you can do your INSERT since you only get the TARGET records back. You can't use the results of the OUTPUT within correlated sub-query from the SOURCE table either. Easiest thing is to use a temp table or table variable to capture the output.

-- Logic to do upsert
DECLARE @Updates TABLE (
    [Name] [varchar](10) NOT NULL,  
    [StartDate] [date] NOT NULL,
    [EndDate] [date] NOT NULL
)

INSERT @Updates
    SELECT
        Name,
        StartDate,
        EndDate
    FROM (
        MERGE INTO @tblEmployee AS TARGET
        USING @typeEmployee AS SOURCE
            ON TARGET.Name = SOURCE.Name 
        WHEN MATCHED AND TARGET.StartDate < SOURCE.StartDate
        THEN
            --First Update Existing Record EndDate to Previous Date as shown below 
            UPDATE SET
                EndDate = DATEADD(DAY, -1, CONVERT(DATE, SOURCE.StartDate))
        WHEN NOT MATCHED BY TARGET -- OR MATCHED AND TARGET.StartDate >= SOURCE.StartDate -- Handle this case?
        THEN
            INSERT VALUES(SOURCE.Name, SOURCE.StartDate, SOURCE.EndDate)
        OUTPUT $action, INSERTED.Name, INSERTED.StartDate, INSERTED.EndDate
        -- Use the MERGE to return all changed records of target table
    ) AllChanges (ActionType, Name, StartDate, EndDate)
    WHERE AllChanges.ActionType = 'UPDATE' -- Only get records that were updated

Now that you've captured the output of the MERGE and filtered to only get updated TARGET records, you can then do your outstanding INSERT by filtering only the SOURCE records that were part of the MERGE update.

INSERT @tblEmployee
    SELECT
        SOURCE.Name,
        SOURCE.StartDate,
        SOURCE.EndDate
    FROM @typeEmployee SOURCE
    WHERE EXISTS (
        SELECT *
        FROM @Updates Updates
        WHERE Updates.Name = SOURCE.Name
            -- Other join conditions to ensure 1:1 match against SOURCE (start date?)
    )

Ouput

This is the output of the sample records after the change. Your intended TARGET changes were made.

-- Show output
SELECT * FROM @tblEmployee

Solidus

Little explanation might be helpful of your above code – Branchiopod

Added additional explanations. Are there any specific question you have? Happy to explain any parts of it... – Solidus

One comment, you can actually insert into the table you are merging into without the need to have the extra @Updates in-memory table. – Mattoid

Answer 2

Following the idea from the accepted answer, this works as well in Sql server 2008 r2:

create table Test1 (
  Id int, FromDate date, ThruDate date, Value int
)


insert into dbo.Test1
 (Id, FromDate, ThruDate, [Value])
select 
  t.Id, t.FromDate, T.ThruDate, t.Value * 100
from (

MERGE  dbo.Test1 AS Target
 USING (
         select 1 as Id, '2000-01-01' as FromDate, '2000-12-31' as ThruDate, 2 as Value
       ) AS Source
    ON (  target.id = source.Id
        )
WHEN MATCHED
THEN
  UPDATE SET Target.[Id] = Source.[Id]
           , Target.[FromDate] = Source.[FromDate]
           , Target.[ThruDate] = Source.[ThruDate]
           , Target.[Value] = Source.[Value]
WHEN NOT MATCHED BY TARGET
THEN
  INSERT ([Id]
        , [FromDate]
        , [ThruDate]
        , [Value])
  VALUES (Source.[Id]
        , Source.[FromDate]
        , Source.[ThruDate]
        , Source.[Value])
OUTPUT $ACTION as Act, Inserted.*
) t
where t.Act  = 'Update'

You can play with different values.

Mattoid

Bonus point for the OUTPUT $ACTION option. That is a great way to test if it worked! Thanks – Slocum