合并数据集时如何为指定行的指定列程序化赋值NULL？

在合并两个数据集时，需要让指定数据集（示例中的#stage2）的所有行在以cost_center开头的列上自动赋值为NULL。现有手动指定NULL的方法在新增cost_center列时无法动态适配，需要一个程序化的解决方案。

示例场景代码 #

select *
into #stage1
from
(
    values
    (42, 170, 44, 827),
    (43, 170, 68, 880),
    (44, 190, 31, 745)
) d (work_center, plant, cost_center3, cost_center14);

select *
into #stage2
from
(
    values
    (10, 200),
    (11, 200),
    (12, 200)
) d (work_center, plant);

-- 手动方法：新增cost_center列时需修改代码
select * from #stage1
union
select *, NULL, NULL from #stage2;

尝试过使用CROSS APPLY但得到交叉连接结果，不符合需求：

select s1.*, s2.*
from #stage1 s1
cross apply #stage2 s2;

解决方案：动态SQL实现自动适配 #

通过动态SQL可以自动识别目标列并拼接查询语句，无需手动维护NULL列表：

DECLARE @costColumns NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- 获取#stage1中所有以cost_center开头的列，拼接为NULL值序列
SELECT @costColumns = STRING_AGG('NULL', ', ')
FROM sys.columns
WHERE object_id = OBJECT_ID('#stage1')
AND name LIKE 'cost_center%';

-- 构造动态合并查询
SET @sql = N'
SELECT * FROM #stage1
UNION ALL
SELECT *, ' + @costColumns + N' FROM #stage2;
';

-- 执行动态SQL
EXEC sp_executesql @sql;

关键说明：

• 使用STRING_AGG（SQL Server 2017及以上版本支持）自动拼接列对应的NULL值；若使用低版本SQL Server，可改用FOR XML PATH方式完成字符串拼接。
• 用UNION ALL替代UNION（除非业务需要去重），能获得更好的执行性能。
• 新增cost_center类列后，无需修改代码，动态SQL会自动识别并添加对应的NULL赋值。

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