这个T-SQL MERGE语句有什么问题？

Question

我刚接触MERGE，我确定我的代码中存在一些错误。

这段代码会运行并创建我的方案：

我有两个表，一个名为TempUpsert的表填充自SqlBulkCopy操作（数亿条记录），另一个是用于索引和使用的生产数据的Sales表。

我希望将TempUpsert表与Sales表合并。

显然我做错了什么，即使是最简单的示例也会失败。

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TempUpsert]') )
drop table TempUpsert;

CREATE TABLE [dbo].[TempUpsert](
      [FirstName] [varchar](200) NOT NULL,
      [LastName] [varchar](200) NOT NULL,
      [Score] [int] NOT NULL
) ON [PRIMARY] ;

CREATE TABLE [dbo].[Sales](
      [FullName] [varchar](200) NOT NULL,
      [LastName] [varchar](200) NOT NULL,
      [FirstName] [varchar](200) NOT NULL,
      [lastUpdated] [date] NOT NULL,
CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED 
(
      [FullName] ASC
)

---- PROC

CREATE PROCEDURE  [dbo].[sp_MoveFromTempUpsert_to_Sales]
(@HashMod int)
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

MERGE Sales AS trget
    USING (

    SELECT 
--- Edit: Thanks to Mikal added DISTINCT
DISTINCT
            FirstName, LastName , [Score], LastName+'.'+FirstName  AS FullName
    FROM TempUpsert AS ups) AS src (FirstName, LastName, [Score], FullName)

    ON 
    (
            src.[Score] = @hashMod 
    AND 
            trget.FullName=src.FullName
    )

    WHEN MATCHED 
        THEN 

        UPDATE SET trget.lastUpdated = GetDate() 

      WHEN NOT MATCHED 
            THEN        INSERT   ([FullName], [LastName], [FirstName], [lastUpdated]) 
      VALUES (FullName, src.LastName, src.FirstName, GetDate())

   OUTPUT $action, Inserted.*, Deleted.* ;
      --print @@rowcount

END

GO

---  Insert dummie data

INSERT INTO TempUpsert (FirstName, LastName, Score)
VALUES ('John','Smith',2);


INSERT INTO TempUpsert (FirstName, LastName, Score)
VALUES ('John','Block',2);


INSERT INTO TempUpsert (FirstName, LastName, Score)
VALUES ('John','Smith',2); --make multiple on purpose

----- EXECUTE PROC
GO


DECLARE     @return_value int

EXEC  @return_value = [dbo].[sp_MoveFromTempUpsert_to_Sales]
            @HashMod = 2

SELECT      'Return Value' = @return_value
GO

这将返回：

（1行受影响）
（1行受影响）
（1行受影响）

消息2627，级别14，状态1，过程sp_MoveFromTempUpsert_to_Sales，第12行
违反了主键约束“PK_Sales”。无法在对象“dbo.Sales”中插入重复键。语句已终止。

（1行受影响）

请问我做错了什么？非常感谢。

- Mickey Perlstein

---

WHEN NOT MATCHED等语句在任何插入操作之前进行评估，如果这是混淆的原因？并不是说一旦插入匹配行，那么将来具有相同PK的行将被视为“MATCHED”。这对于万圣节保护是必需的。 - Martin Smith

当匹配成功时，我希望更新[lastupdated]字段；当匹配失败时，我希望在销售表中插入一条新记录。我不能从临时更新表（TempUpsert）中挑选和选择我的输入。 - Mickey Perlstein

Answer 1

你的暂存表中的前两行将会显示主键冲突。Conc是主键，并且你插入了两个值完全相同的tmain+dmain。

- Mikael Eriksson

---

抱歉，我该怎么做？按组分组？去重？ - Mickey Perlstein

你可以根据名字和姓氏进行分组。而且你需要在分数上使用聚合函数（最大值、最小值...）。 - Mikael Eriksson

分数并不是真正的分数，而是一个哈希函数，用于将巨大的记录集分成块，以避免使Sql服务器崩溃。关于Group by，它比DISTINCT更好（我相信你的话 :)）。 - Mickey Perlstein

如果您在目标表中没有与要求哈希码上的全名匹配的行，则这是可以预料到的情况。 - Mikael Eriksson

哦，我不断运行删除表格的脚本，因此总是进行插入操作。谢谢您 :) - Mickey Perlstein

Answer 2

总结

MERGE 要求其输入（Using）不包含重复项，Using 是一个常规的 SQL 语句，因此您可以使用 Group By、distinct 和 having，以及 Where 子句。

我的最终 Merge 如下：

MERGE Sales AS trget
    USING (

    SELECT     FirstName, LastName, Score, LastName + '.' + FirstName AS FullName
    FROM         TempUpsert AS ups
    WHERE Score = @hashMod  
    GROUP BY FirstName, LastName, Score, LastName + '.' + FirstName

    ) AS src (FirstName, LastName, [Score], FullName)


    ON 
    (
    --        src.[Score] = @hashMod 
    --AND 
            trget.FullName=src.FullName
    )

    WHEN MATCHED 
        THEN 

        UPDATE SET trget.lastUpdated = GetDate() 


      WHEN NOT MATCHED 
            THEN        INSERT   ([FullName], [LastName], [FirstName], [lastUpdated]) 
      VALUES (FullName, src.LastName, src.FirstName, GetDate())

   OUTPUT $action, Inserted.*, Deleted.* ;
      --print @@rowcount

END

而且它正常工作！

感谢你们所有人 :)

- Mickey Perlstein

Answer 3

在MERGE的USING部分使用子查询时，如果没有DISTINCT或适当的聚合函数，则会有两行符合MERGE的ON部分使用的条件，这是不允许的。（两个John.Smith）

并且

将条件src.[Score] = @hashMod移至子查询中，

如果ON子句不成功，例如John.Smith的得分为2，而@HashMod = 1-则如果您已经在目标表中拥有John.Smith的行，则会出现主键约束错误。

- Oleg Dok

---

谢谢Oleg，目前我理解了。我现在的问题是合并现在是一个复杂的插入系统，但它从来没有更新，只有插入。 - Mickey Perlstein

请查看您在@hashMod中提供的内容。 - Oleg Dok