SQL Server 2019中如何快速合并动态生成的同结构多表数据？

你当前通过游标拼接UNION ALL的思路是可行的，但针对500+表的场景，有更高效简洁的实现方式：

1. 用STRING_AGG直接聚合SQL语句（SQL Server 2017及以上版本） #

利用SQL Server原生的字符串聚合函数STRING_AGG，替代游标循环拼接，性能和代码简洁度都更优，还能避免游标带来的额外开销：

DECLARE @SQL NVARCHAR(MAX) = '';

SELECT @SQL = STRING_AGG('SELECT * FROM ' + QUOTENAME(TABLE_NAME), ' UNION ALL ')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' 
  AND TABLE_CATALOG = 'z_scope' 
  AND TABLE_NAME LIKE 'CreateOrderRequestPending_TD001_%';

EXEC sp_executesql @SQL;

• QUOTENAME函数用于包裹表名，避免表名含特殊字符（如空格、关键字）导致语法错误；
• STRING_AGG会自动将所有表的查询语句用UNION ALL连接，无需手动处理循环和拼接逻辑。

2. 兼容低版本SQL Server的FOR XML PATH拼接方案 #

如果使用SQL Server 2017之前的版本，可通过FOR XML PATH实现字符串聚合：

DECLARE @SQL NVARCHAR(MAX) = '';

SELECT @SQL = STUFF(
    (SELECT ' UNION ALL SELECT * FROM ' + QUOTENAME(TABLE_NAME)
     FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_TYPE = 'BASE TABLE' 
       AND TABLE_CATALOG = 'z_scope' 
       AND TABLE_NAME LIKE 'CreateOrderRequestPending_TD001_%'
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
    1, 10, '' -- 移除开头多余的" UNION ALL "
);

EXEC sp_executesql @SQL;

针对复杂查询的优化提示 #

如果你的实际查询需要关联主表，建议将关联逻辑嵌入每个子查询中，例如：

SELECT @SQL = STRING_AGG(
    'SELECT t.*, m.master_col FROM ' + QUOTENAME(TABLE_NAME) + ' t JOIN master_table m ON t.id = m.id', 
    ' UNION ALL '
)
FROM INFORMATION_SCHEMA.TABLES
WHERE ... -- 过滤条件不变

若主表数据量较大，也可以考虑先合并所有子表数据到临时表，再与主表关联，减少重复关联的开销。

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