SQL Server 批量核对查询实战：临时表 + SqlBulkCopy + JOIN 一次比对十万级数据

一、从一个常见的性能问题说起

业务系统里经常遇到这样的核对场景：用户上传一份 Excel（几千到几万行），系统要把每一行的关键字段（号码、设备编号、策略名……）拿到数据库里查一遍——查它存不存在、匹配到几条记录，再把结果逐行回填给前端展示。

最直觉的写法是循环单条查询：

foreach (DataRow row in sourceTable.Rows)
{
    using var cmd = new SqlCommand(
        "SELECT * FROM [Target] WHERE MSISDN = @msisdn", conn);
    cmd.Parameters.AddWithValue("@msisdn", row["MSISDN"]);
    // 每行一次网络往返
}

一万行数据就是一万次网络往返。就算单次查询只要 5ms，串行执行也要接近一分钟，而数据库连接池在这种高频短查询下很容易被压垮。这就是典型的 N+1 查询问题在批量核对场景下的翻版。

解决思路其实不复杂：把”带着数据去查”变成”把数据先搬过去，再用一次 JOIN 查完”。本文基于项目里真实落地的 BulkQueryData 实现，拆解这套「临时表 + SqlBulkCopy + JOIN」批量核对方案的完整设计。

二、方案演进：为什么选临时表

批量核对有三种常见方案，各有取舍：

临时表方案的灵活性在于：临时表从目标表克隆结构而来，字段类型天然一致；JOIN 可以用任意多个条件列匹配，不受参数数量限制；查询结果通过”行号列”精确映射回源表每一行。

整体流程如下：

flowchart LR
    A[源数据 DataTable<br/>Excel / 前端导入] --> B[附加行号列<br/>ROW_INDEX]
    B --> C[CloneTable 克隆结构<br/>SELECT INTO WHERE 1=2]
    C --> D[BulkCopy 装载<br/>单次网络往返]
    D --> E[JOIN 核对查询<br/>空值等价匹配]
    E --> F[按行号统计回填<br/>总数 / 有效行]
    F --> G[finally 删除临时表]

三、关键实现拆解

3.1 生成唯一临时表名

并发场景下多个请求可能同时做核对，临时表名必须互不冲突。用 DateTime.Now.Ticks 拼接即可，精度足够高，几乎不可能重复：

public override string CreateTempTableName(bool isDatabaseTempTable)
{
    // Ticks 保证并发下的表名唯一
    return isDatabaseTempTable
        ? "#Temp" + DateTime.Now.Ticks
        : "Temp" + DateTime.Now.Ticks;
}

这里选的是普通物理临时表（不带 # 前缀），配合 finally 中的显式删除来清理。相比会话级 #temp 表，物理表不受连接生命周期约束，跨多次连接操作更稳。

3.2 结构克隆：SELECT INTO … WHERE 1=2

临时表的结构必须和目标表完全一致，否则 JOIN 匹配时会出现隐式类型转换，轻则索引失效，重则报错。项目里用 SELECT INTO ... WHERE 1=2 一条 SQL 完成结构克隆——只复制列定义，不复制任何数据：

builder.AppendFormat("IF OBJECT_ID('[{0}]', 'U') IS NOT NULL", targetTableName).AppendLine();
builder.AppendFormat("DROP TABLE [{0}]", targetTableName).AppendLine();

builder.Append("SELECT ");
foreach (string columnName in columnNames)
{
    if (columnName == name) // identity 列：CONVERT 生成常量列绕开自增限制
    {
        builder.AppendFormat("CONVERT({0}, 0) AS [{1}],", type, columnName);
    }
    else
    {
        builder.AppendFormat("[{0}],", columnName);
    }
}
builder.Remove(builder.Length - 1, 1);
builder.AppendFormat(" INTO [{1}] FROM [{0}] WHERE 1=2", sourceTableName, targetTableName).AppendLine();

这里有两个细节值得注意：

1. identity 列的处理：SELECT INTO 复制表时会连带 identity 属性一起带过去，而自增列不允许显式插入。解决办法是在 SELECT 列表里用 CONVERT(类型, 0) 把它变成一个普通的常量列，identity 属性自然消失，之后再用 ALTER COLUMN 补回 NOT NULL 约束。
2. 先删后建：开头先 IF OBJECT_ID ... DROP TABLE，防止前一次执行异常退出遗留的脏表导致本次失败。

3.3 行号列：JOIN 结果如何对应回源表

这是整个方案最巧妙的一环。JOIN 查询返回的是”目标表匹配行 × 临时表源行”的笛卡尔结果，我们怎么知道返回的每一行对应源表的第几行？

答案是：装载前先给源数据附加一个行号列，让它跟着 BulkCopy 一起进临时表，JOIN 后随结果集带回来：

// 给源表附加行号列，作为 JOIN 结果回传的"行索引"
string rowIndexColumnName = queryTable.GetAvailableColumnName(BusinessCommon.ROW_INDEX_COLUMN_NAME);
queryTable.Columns.Add(rowIndexColumnName, typeof(Int32));
for (int i = 0; i < queryTable.Rows.Count; i++)
{
    queryTable.Rows[i][rowIndexColumnName] = i + 1;
}

database.CloneTable(tableName, tempTableName, queryTable.GetColumnNames()); // 克隆结构
database.AddColumn(tempTableName, rowIndexColumnName, DbType.Int32, false, 4);
database.BulkCopy(queryTable, tempTableName); // 万行级装载只需一次往返

GetAvailableColumnName 会先检查列名冲突，避免和业务字段重名。临时表建好后也通过 AddColumn 补上这个行号列，保证 BulkCopy 列对列写入不缺列。

3.4 JOIN 查询：空值等价匹配

核对条件的拼接看起来简单，但有一个坑：SQL 里 NULL = NULL 的结果是 UNKNOWN，普通的 A.col = B.col 会把两边都是空的行漏掉。而核对场景里”源数据为空、库里也为空”恰恰要算匹配成功。所以条件要写成空值等价形式：

SELECT A.*, B.[ROW_INDEX_1]
FROM [TargetTable] AS A, [Temp1750000000000] AS B
WHERE ((A.[MSISDN] IS NULL AND (B.[MSISDN] IS NULL OR B.[MSISDN] = ''))
        OR A.[MSISDN] = B.[MSISDN])
  AND ((A.[IMSI]   IS NULL AND (B.[IMSI]   IS NULL OR B.[IMSI] = ''))
        OR A.[IMSI] = B.[IMSI])
ORDER BY A.[MSISDN], B.[ROW_INDEX_1]

同时把行号列加进 ORDER BY，保证结果按源行有序，方便后续按行分组统计。

3.5 统计回填与去重

拿到 JOIN 结果后，从最后一列读出行号，把每条匹配记录归位到源表对应行：

totalRowCounts = new int[queryTable.Rows.Count];
validRowIndices = new List<int>[queryTable.Rows.Count];

// 第一遍：统计每行的匹配总数
for (int i = 0; i < resultTable.Rows.Count; i++)
{
    int rowIndex = (int)resultTable.Rows[i][resultTable.Columns.Count - 1] - 1;
    totalRowCounts[rowIndex]++;
}

// 按主键去重（忽略大小写），只保留每组第一条
BusinessCommon.RemoveRepeatRows(resultTable, primaryKeys, true);

// 第二遍：在去重后的结果里收集每行对应的有效行下标
for (int i = 0; i < resultTable.Rows.Count; i++)
{
    int rowIndex = (int)resultTable.Rows[i][resultTable.Columns.Count - 1] - 1;
    validRowIndices[rowIndex].Add(i);
}

这里要区分两个统计口径：totalRowCounts 是”在库里匹配到几条”（原始计数），validRowIndices 是”去重后每行对应结果表里的哪些下标”。前端展示时既可以看到”重复了几条”，又能点开看去重后的明细。

3.6 临时表生命周期：try/finally 兜底

临时表是共享资源，绝不能因为一次异常就永久残留。整个查询包在 try/finally 里，无论成败都删除临时表：

string tempTableName = database.CreateTempTableName(false);
try
{
    // 克隆结构 → 加行号列 → BulkCopy → JOIN 查询 → 统计回填
}
finally
{
    database.DeleteTable(tempTableName); // 无论成败都清理
}

DeleteTable 内部同样先 IF OBJECT_ID ... DROP，做到幂等——即使表已经不存在也不会报错。

四、为什么这样快

对比一下三种方式的网络往返次数（以 1 万行源数据为例）：

• 循环单条查询：10000 次往返；
• 临时表方案：克隆结构 1 次 + 加列 1 次 + BulkCopy（默认 BatchSize=10000）1~2 次 + JOIN 查询 1 次 + 删表 1 次 ≈ 5~6 次往返。

更重要的是，重活全部下推到数据库引擎内部完成：JOIN 在库内走索引、一次执行完毕，中间结果不经过应用层，省掉的不只是网络耗时，还有应用侧逐行组包、参数绑定、结果解析的开销。实测中万行级核对从分钟级降到秒级。

五、小结

这套「临时表 + SqlBulkCopy + JOIN」批量核对方案的核心技巧可以归纳为四点：

1. SELECT INTO … WHERE 1=2 结构克隆，一条 SQL 复制表结构，CONVERT 处理 identity 列；
2. 行号列回传，用附加的 ROW_INDEX 列把 JOIN 结果精确映射回源表每一行，这是批量核对场景的通用技巧；
3. 空值等价匹配，((A.c IS NULL AND ...) OR A.c = B.c) 避免 NULL 比较漏行；
4. try/finally 幂等清理，临时表无论成败都删除，保证并发安全。

适用边界也要心里有数：它适合”一次性批量核对”场景；如果核对是高频常态化的，更应该考虑在目标表上建好索引、用持久化中间表或物化视图来承接。工具没有银弹，理解代价模型才能选对方案。