Обновление и вставка при совпадении условия в TSQL-Merge

Это не то, для чего предназначено MERGE (обновление и вставка в том же предложении). Для этого вы можете использовать предложение OUTPUT, чтобы получить только все обновленные записи. Комбинация MERGE/OUTPUT очень разборчива. Ваши обновления OUTPUT на самом деле являются целевыми записями, которые были обновлены, поэтому вам нужно запустить целевые записи в переменной temp/table. Затем вы сопоставляете их с ИСТОЧНИКОМ, чтобы выполнить ВСТАВКУ. Вам не разрешат объединять выходные результаты непосредственно с источником или даже использовать в качестве коррелированного подзапроса в WHERE.

Настройте некоторые образцы данных

Приведенный ниже код просто устанавливает некоторые примеры данных.

-- 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')

Обновления хранимой процедуры

Вы можете использовать OUTPUT в конце MERGE, чтобы он возвращал измененные записи целевых записей, и, включив $action, вы также получите, была ли это вставка, обновление или удаление.

Однако набор результатов из MERGE / OUTPUT нельзя напрямую соединить с таблицей SOURCE, поэтому вы можете выполнить INSERT, так как вы получите только записи TARGET. Вы также не можете использовать результаты OUTPUT в коррелированном подзапросе из таблицы SOURCE. Проще всего использовать временную таблицу или табличную переменную для захвата вывода.

-- 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

Теперь, когда вы перехватили выходные данные MERGE и отфильтровали, чтобы получить только обновленные записи TARGET, вы можете выполнить оставшуюся работу INSERT, отфильтровав только ИСТОЧНИКИ, которые были частью обновления MERGE.

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?)
    )

Вывод

Это результат выборки записей после изменения. Запланированные изменения TARGET были внесены.

-- Show output
SELECT * FROM @tblEmployee

Следуя идее из принятого ответа, это работает и на сервере Sql 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'

Вы можете играть с разными значениями.