SQL Server中列数不一致的多表UNION如何自动填充NULL？

SQL Server原生UNION ALL语法要求所有查询分支的列数量、顺序、数据类型严格对齐，没有内置自动补全缺失列的功能，但可以通过系统视图动态生成合并SQL，完全不需要逐行手写NULL补全逻辑，哪怕表和列数量很大也能快速完成。

核心实现逻辑 #

• 先从系统视图提取所有待合并表的全量唯一列清单，同步记录每个列的对应数据类型，避免UNION时出现类型不匹配错误
• 逐表生成SELECT子句：当前表存在的列直接引用，不存在的列自动生成CAST(NULL AS 对应类型) AS 列名的补全片段
• 把所有表的SELECT片段用UNION ALL拼接为完整可执行语句

可直接复用的脚本 #

你只需要在脚本开头填入需要合并的10张表的架构和表名，剩下的补全工作会自动完成：

-- 1. 填入所有待合并的表（架构.表名格式）
DECLARE @MergeTables TABLE (SchemaName NVARCHAR(128), TableName NVARCHAR(128))
INSERT INTO @MergeTables VALUES
('dbo', 'TABLE1'),
('dbo', 'TABLE2'),
('dbo', 'TABLE3')
-- 按上述格式补充剩余7张表即可

-- 2. 收集所有表的去重列清单及对应数据类型
DECLARE @FullColumns TABLE (
    ColName NVARCHAR(128),
    ColType NVARCHAR(MAX),
    SortID INT
)
INSERT INTO @FullColumns
SELECT 
    c.name ColName,
    CONCAT(t.name,
        CASE WHEN t.name IN ('varchar','char','nvarchar','nchar') 
            THEN CONCAT('(', IIF(c.max_length=-1,'MAX',CAST(IIF(t.name LIKE 'n%',c.max_length/2,c.max_length) AS NVARCHAR)), ')')
             WHEN t.name IN ('decimal','numeric') THEN CONCAT('(',c.precision,',',c.scale,')')
             ELSE '' END
    ) ColType,
    ROW_NUMBER() OVER(ORDER BY c.name) SortID
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
JOIN @MergeTables mt ON c.object_id = OBJECT_ID(CONCAT(mt.SchemaName,'.',mt.TableName))
GROUP BY c.name, t.name, c.max_length, c.precision, c.scale

-- 3. 逐表生成SELECT片段，缺失列自动补类型匹配的NULL
DECLARE @SqlParts TABLE (Part NVARCHAR(MAX), SortID INT)
;WITH TableColMap AS (
    SELECT mt.SchemaName, mt.TableName, c.name ColName
    FROM sys.columns c
    JOIN @MergeTables mt ON c.object_id = OBJECT_ID(CONCAT(mt.SchemaName,'.',mt.TableName))
)
INSERT INTO @SqlParts
SELECT 
    CONCAT(
        'SELECT ',
        STRING_AGG(IIF(tcm.ColName IS NOT NULL, fc.ColName, CONCAT('CAST(NULL AS ',fc.ColType,') AS ',fc.ColName)), ', ')
            WITHIN GROUP (ORDER BY fc.SortID),
        ' FROM ', mt.SchemaName, '.', mt.TableName
    ) Part,
    ROW_NUMBER() OVER(ORDER BY mt.SchemaName, mt.TableName) SortID
FROM @MergeTables mt
CROSS JOIN @FullColumns fc
LEFT JOIN TableColMap tcm 
    ON mt.SchemaName = tcm.SchemaName 
    AND mt.TableName = tcm.TableName 
    AND fc.ColName = tcm.ColName
GROUP BY mt.SchemaName, mt.TableName

-- 4. 拼接为完整UNION ALL语句
DECLARE @FinalSql NVARCHAR(MAX)
SELECT @FinalSql = STRING_AGG(Part, ' UNION ALL ') WITHIN GROUP (ORDER BY SortID)
FROM @SqlParts

-- 先打印生成的语句核对，确认无误后再执行
PRINT @FinalSql
-- EXEC sp_executesql @FinalSql

使用注意事项 #

• 脚本自动做了类型匹配，不会出现NULL无对应类型导致的UNION报错
• 强烈建议先通过PRINT输出检查生成的SQL，确认列逻辑正确后再执行
• 如果需要区分数据来源，可以在SELECT片段中增加常量列'对应表名' AS SourceTable，方便后续可视化时做数据溯源
• 如果涉及跨库、跨链接服务器的表，只需要调整@MergeTables里的表名写法，同步修改OBJECT_ID的取值逻辑即可，核心逻辑不需要改动

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