SQL Server：高效获取多列匹配数据主键并实现去重存储

问题背景 #

业务流程要求每条唯一数据仅在DataTable中存储一行，通过主键DataID引用该数据集；同时用ReceivedData表跟踪每日接收的数据记录——无论数据是新增还是已存在，都要记录对应的DataID。当前用EXCEPT插入新数据的方式只能获取新增记录的DataID，无法捕获已存在匹配记录的DataID，全列连接查询效率又过低，需要更优解决方案。

核心解决方案：使用MERGE语句 #

MERGE是SQL Server专门用于合并数据的语句，能在单次操作中完成匹配检查、插入新记录，并且可通过OUTPUT子句同时捕获已存在记录和新增记录的DataID，配合唯一索引能大幅提升效率。

步骤1：创建唯一索引（关键优化） #

先在DataTable的唯一标识列上创建唯一非聚集索引，确保MERGE的匹配操作是高效的索引查找，而非全表扫描：

CREATE UNIQUE NONCLUSTERED INDEX IX_DataTable_Unique 
ON DataTable (name, birthday, value1, value2);

（注：实际业务中需替换为判定数据唯一性的所有列）

步骤2：用MERGE合并数据并捕获所有DataID #

使用表变量存储MERGE输出的结果，包含所有接收数据对应的DataID：

-- 定义表变量存储接收数据的对应DataID
DECLARE @ReceivedDataIDs TABLE (
    DataID bigint
);

MERGE INTO DataTable AS Target
USING NewDataTable AS Source
-- 匹配条件：判定数据唯一性的列
ON Target.name = Source.name 
   AND Target.birthday = Source.birthday 
   AND Target.value1 = Source.value1 
   AND Target.value2 = Source.value2
-- 无匹配时插入新记录
WHEN NOT MATCHED THEN
    INSERT (name, birthday, value1, value2)
    VALUES (Source.name, Source.birthday, Source.value1, Source.value2)
-- 输出所有涉及记录的DataID：匹配时取已存在的deleted.DataID，插入时取新增的inserted.DataID
OUTPUT ISNULL(inserted.DataID, deleted.DataID) INTO @ReceivedDataIDs;

步骤3：写入ReceivedData表 #

直接从表变量中取出所有DataID，插入到ReceivedData：

INSERT INTO ReceivedData (ReceivedDate, DataID)
SELECT '2023-05-02 03:00:00', DataID
FROM @ReceivedDataIDs;

效果验证 #

按照示例数据执行后：

• DataTable会新增Alice的记录，DataID为4（假设DataID是自增主键）
• ReceivedData会新增3条记录，分别关联DataID=1（John）、DataID=3（Steve）、DataID=4（Alice），完全符合需求。

方案优势 #

1. 高效性：依托唯一索引，MERGE的匹配操作是O(log n)的索引查找，远快于全列连接
2. 原子性：单次MERGE操作完成匹配和插入，避免多步操作的事务风险
3. 完整性：一次性捕获所有接收数据的DataID，无需额外查询已存在记录

内容的提问来源于stack exchange，提问作者SGP