在TSQL合并中匹配条件时进行更新和插入 [英] Update and Insert When Condition is Matched in TSQL-Merge

问题描述

我一直在尝试编写存储过程，在此过程中我可以使用具有以下条件的合并来执行UpSert

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

1. 如果存在记录，则将Target的EndDate更改为昨天的日期，即Present Day-1

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

如果不存在记录，则插入新记录

If Record is not Present then Insert New Record

这是我在SP中使用的表tblEmployee

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
)

这是我的SP，它以UDTT作为输入参数

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

能否请我解释一下TSQL中合并的执行流程，即

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

每次都在合并中执行以上所有3个条件，或者每次仅执行匹配条件

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

预先感谢

推荐答案

这不是MERGE的目的(在同一子句中更新并插入).为此，可以使用OUTPUT子句仅获取所有更新的记录. MERGE/OUTPUT组合非常挑剔.您的OUTPUT更新实际上是已更新的TARGET记录，因此您必须在temp/table变量中启动TARGET记录.然后，将它们与源匹配，以进行INSERT.在WHERE中，您将不允许将输出结果直接添加回源，甚至不能用作相关子查询.

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.

设置一些示例数据

下面的代码仅设置了一些示例数据.

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

更新到存储过程

您可以在MERGE的末尾使用OUTPUT使其返回目标记录的修改后的记录，并且通过包含$action，您还将获得它是插入，更新还是删除的信息.

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.

但是，MERGE/OUTPUT的结果集不能直接与SOURCE表连接，因此您可以执行INSERT，因为您只返回了TARGET记录.您也不能在SOURCE表的相关子查询中使用OUTPUT的结果.最简单的方法是使用临时表或表变量来捕获输出.

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

现在，您已经捕获了MERGE的输出并进行了过滤以仅获取更新的TARGET记录，然后可以通过仅过滤属于MERGE更新的SOURCE记录来执行出色的INSERT.

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

输出

这是更改后样本记录的输出.您已按预期更改了TARGET.

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

-- Show output
SELECT * FROM @tblEmployee

这篇关于在TSQL合并中匹配条件时进行更新和插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！