使用T-SQL Merge语句时如何避免插入重复记录

Question

我试图使用T-SQL的MERGE语句插入许多记录，但是当源表中有重复的记录时，我的查询无法插入。失败原因是：

1. 目标表有一个基于两列的主键
2. 源表可能包含重复记录，这些记录违反了目标表的主键约束（引发了“违反主键约束”）

我正在寻找一种方法来更改我的MERGE语句，以便它要么忽略源表中的重复记录，并且/或者将尝试/捕获INSERT语句以捕获可能发生的异常（即，所有其他INSERT语句都将运行，而无论可能会出现一些坏蛋）-或者，也许有更好的方法来解决此问题？

这是我要解释的查询示例。以下示例将向临时表中添加100k条记录，然后尝试将这些记录插入目标表中-

编辑
在我的原始帖子中，我仅在示例表中包括两个字段，这些字段让SO朋友提供了DISTINCT解决方案，以避免在MERGE语句中出现重复。我应该提到，在我的实际问题中，表有15个字段，而在这15个字段中，两个字段是集群主键。因此DISTINCT关键字不起作用，因为我需要选择所有15个字段，并忽略基于其中两个字段的重复项。

我更新了下面的查询，以包含更多字段col4。我需要在合并中包含col4，但只需要确保col2和col3是唯一的即可。

-- Create the source table
CREATE TABLE #tmp (
col2 datetime NOT NULL,
col3 int NOT NULL,
col4 int
)
GO

-- Add a bunch of test data to the source table
-- For testing purposes, allow duplicate records to be added to this table
DECLARE @loopCount int = 100000
DECLARE @loopCounter int = 0
DECLARE @randDateOffset int
DECLARE @col2 datetime
DECLARE @col3 int
DECLARE @col4 int

WHILE (@loopCounter) < @loopCount
BEGIN
    SET @randDateOffset = RAND() * 100000
    SET @col2 = DATEADD(MI,@randDateOffset,GETDATE())
    SET @col3 = RAND() * 1000
    SET @col4 = RAND() * 10
    INSERT INTO #tmp
    (col2,col3,col4)
    VALUES
    (@col2,@col3,@col4);

    SET @loopCounter = @loopCounter + 1
END

-- Insert the source data into the target table
-- How do we make sure we don't attempt to INSERT a duplicate record? Or how can we 
-- catch exceptions? Or?
MERGE INTO dbo.tbl1 AS tbl
    USING (SELECT * FROM #tmp) AS src
    ON (tbl.col2 = src.col2 AND tbl.col3 = src.col3)
    WHEN NOT MATCHED THEN 
        INSERT (col2,col3,col4)
        VALUES (src.col2,src.col3,src.col4);
GO

Answer 1

解决了您的新规范。只插入最高的col4值：这次我使用group by来防止重复的行。

MERGE INTO dbo.tbl1 AS tbl 
USING (SELECT col2,col3, max(col4) col4 FROM #tmp group by col2,col3) AS src 
ON (tbl.col2 = src.col2 AND tbl.col3 = src.col3) 
WHEN NOT MATCHED THEN  
    INSERT (col2,col3,col4) 
    VALUES (src.col2,src.col3,src.col4);