在TSQL-Merge中，当条件匹配时更新和插入数据

Question

在TSQL-Merge中，当条件匹配时更新和插入数据

sql-serverstored-proceduressql-server-2014sql-merge

4

我一直在尝试编写一个存储过程，使用Merge执行UpSert，并满足以下条件：

如果记录已存在，则将目标的EndDate更改为昨天，即当前日期-1
如果记录不存在，则插入新记录

这是我在SP中使用的tblEmployee表：

CREATE TABLE tblEmployee
(
    [EmployeeID] [int] IDENTITY(1,1) NOT NULL, 
    [Name] [varchar](10) NOT NULL,  
    [StartDate] [date] NOT NULL,
    [EndDate] [date] NOT NULL
)

这是我的存储过程，它以UDTT作为输入参数。

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

如何在匹配某一列时同时进行现有记录的更新和新记录的添加

请问有人可以向我解释 TSQL 中 Merge 的执行流程吗？

    WHEN MATCHED --Will this Execute Everytime

    WHEN NOT MATCHED by TARGET -- Will this Execute Everytime

    WHEN NOT MATCHED by SOURCE -- Will this Execute Everytime

以上三个条件在合并过程中每次都会执行，还是只有匹配条件会每次执行？感谢提前帮助。

- Amar

当匹配时，执行以下操作：/* 在此处更新和插入 */ end - Zohar Peled

编译错误：“Incorrect Syntax near 'Begin'. Expecting DELETE, INSERT, or UPDATE.” - Amar

好吧，我想我错了... - Zohar Peled

2个回答

4

按照被接受的答案的思路，这种方法在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'

您可以尝试使用不同的值。

- boggy

奖励点给$ACTION选项。这是一个很好的测试是否成功的方法！谢谢 - undefined

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Jason W · Accepted Answer

这不是MERGE语句所要做的事情（在同一条子句中更新和插入）。为了实现这个功能，您可以使用OUTPUT 子句只获取所有已更新的记录。 MERGE/OUTPUT组合非常挑剔。您的 OUTPUT 更新实际上是已更新的目标记录，所以您必须将目标记录存储在一个临时/表变量中。然后，您将这些记录与源匹配以进行插入。您将无法直接将输出结果与源连接或在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')

存储过程更新

您可以在MERGE末尾使用OUTPUT，使其返回目标记录的修改记录，并包括$action，还可了解到是插入，更新还是删除操作。

但是，MERGE / OUTPUT的结果集不能直接与SOURCE表联接，因此您无法对其进行INSERT操作，因为只有获取到TARGET记录。您也不能在SOURCE表的相关子查询中使用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 的输出并过滤出仅更新目标记录，接下来您可以通过仅筛选属于 MERGE 更新的源记录来完成未完成的 INSERT。

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

输出

这是更改后样本记录的输出。您预期的目标更改已经完成。

-- Show output
SELECT * FROM @tblEmployee