如何高效将多个SQL Server资产表合并为统一Asset表？

我有多个SQL Server资产数据源，每个表列数不同，但仅需关注少量字段，每个数据源约2-3k行数据。希望将这些数据合并到单个Asset表（示例中的tblAll），存储资产基础信息及来源。若资产名称已存在则更新来源字段（如追加标识），不存在则插入新行。此前用代码遍历实现速度较慢，现寻求高效SQL解决方案。

原伪代码 #

select name, os, user, source 
from tblLSAssets 

and insert into TblALL

if tblAZ.name exists in tblAll 
    update that row with tblAll.source +="AZ" (or use separate col)

if tblAZ.name does NOT exists in tblAll 
    add a new row to tblAll with tblAZ.name, tblAZ.os etc. update source col.

重复上述步骤处理每个资产数据源，若用多列存储各数据源信息更简便也可接受。

示例数据源表 #

tblLSAssets表：

tblAZ表：

预期结果表（tblAll）： #

高效SQL解决方案 #

针对SQL Server，推荐使用MERGE语句实现批量插入/更新，比逐行遍历效率提升显著。以下是针对示例数据源的实现逻辑，其他数据源可复用类似写法：

1. 先创建目标表（若未存在）

CREATE TABLE tblAll (
    name VARCHAR(50) PRIMARY KEY, -- 以name作为主键确保资产唯一性
    OS VARCHAR(50),
    [user] VARCHAR(50), -- 规避关键字冲突
    source VARCHAR(100)
);

2. 合并tblLSAssets数据

MERGE INTO tblAll AS target
USING (
    SELECT name, OS, [user], 'LS' AS source
    FROM tblLSAssets
) AS source ON target.name = source.name
WHEN NOT MATCHED THEN
    INSERT (name, OS, [user], source)
    VALUES (source.name, source.OS, source.[user], source.source)
WHEN MATCHED THEN
    UPDATE SET target.source = CASE 
        WHEN CHARINDEX('LS', target.source) = 0 THEN target.source + '+LS' 
        ELSE target.source 
    END;

3. 合并tblAZ数据

MERGE INTO tblAll AS target
USING (
    SELECT name, OS, [user], 'AZ' AS source
    FROM tblAZ
) AS source ON target.name = source.name
WHEN NOT MATCHED THEN
    INSERT (name, OS, [user], source)
    VALUES (source.name, source.OS, source.[user], source.source)
WHEN MATCHED THEN
    UPDATE SET target.source = CASE 
        WHEN CHARINDEX('AZ', target.source) = 0 THEN target.source + '+AZ' 
        ELSE target.source 
    END;

4. 多列存储来源的替代方案

如果觉得字符串追加的方式不够灵活，可改用多布尔列存储来源标识，后续查询和维护更高效：

-- 修改表结构添加来源标识列
ALTER TABLE tblAll 
ADD is_from_LS BIT DEFAULT 0,
    is_from_AZ BIT DEFAULT 0;

-- 合并tblLSAssets数据
MERGE INTO tblAll AS target
USING (
    SELECT name, OS, [user]
    FROM tblLSAssets
) AS source ON target.name = source.name
WHEN NOT MATCHED THEN
    INSERT (name, OS, [user], is_from_LS)
    VALUES (source.name, source.OS, source.[user], 1)
WHEN MATCHED THEN
    UPDATE SET target.is_from_LS = 1;

-- 合并tblAZ数据
MERGE INTO tblAll AS target
USING (
    SELECT name, OS, [user]
    FROM tblAZ
) AS source ON target.name = source.name
WHEN NOT MATCHED THEN
    INSERT (name, OS, [user], is_from_AZ)
    VALUES (source.name, source.OS, source.[user], 1)
WHEN MATCHED THEN
    UPDATE SET target.is_from_AZ = 1;

方案优势 #

• MERGE是SQL Server原生批量操作，一次性处理整表数据，避免循环遍历的性能开销。
• 逻辑清晰，可快速扩展至更多数据源。
• 多列存储来源的方式，更便于后续筛选、统计等操作。

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