SQL Server动态循环生成表后用UNION ALL合并为单表的需求

针对你的需求，提供两种实现方式：一种保留原有的分时间段数据表生成逻辑，再合并；另一种直接生成合并后的最终表（更高效）。

方式一：保留中间表，后续合并 #

先按原逻辑生成各时间段的数据表，再通过动态SQL拼接UNION ALL语句合并为单个表：

DECLARE @Interval_List as TABLE (index_1 int, Interval VARCHAR(50), Interval_2 VARCHAR(50), From_date date, To_date date)
INSERT INTO @Interval_List VALUES (1,  '2021_Q3',          '2021 Q3',        '2021-07-01', '2021-09-30')
INSERT INTO @Interval_List VALUES (2,  '2021_Q4',          '2021 Q4',        '2021-10-01', '2021-12-31')
INSERT INTO @Interval_List VALUES (3,  '2021_H2',          '2021 H2',        '2021-07-01', '2021-12-31')
INSERT INTO @Interval_List VALUES (4,  '2021',             '2021',           '2021-07-01', '2021-12-31')

DECLARE @StartDate AS DATE
DECLARE @EndDate AS DATE
DECLARE @index_first int
declare @index_last int
declare @interval VARCHAR(50)
declare @interval_2 VARCHAR(50)
declare @issue_table nvarchar(max)

SELECT @index_first = min(index_1),  @index_last = max(index_1) FROM @Interval_List

SET @issue_table = 'dbo.table1'

-- 生成各时间段数据表
WHILE (@index_first <= @index_last)
BEGIN
    SELECT @StartDate = From_date,  @EndDate = To_date, @interval = Interval, @interval_2 = Interval_2 FROM @Interval_List where index_1 = @index_first

    IF OBJECT_ID(@issue_table) IS NULL
    BEGIN
        RAISERROR('无效对象！', 11, 1);
        RETURN;
    END

    declare @query nvarchar(max);

    set @query = 
    N'
    SELECT 
    A.SERVICE,
    A.Service_Group,
    A.Portfolio,
    Interval,
    SUM(Metric_1_Dividend_C_New) AS Metric_1_Dividend_C_New
    INTO dbo.METRIC_1_' + @interval + N'
    FROM
    (
    SELECT 
    ISSUE_CREATION_DATE,
    ISSUE_ID,
    SERVICE,
    SERVICE_GROUP,
    PORTFOLIO,
    @Interval_2 as Interval,
    AVG(Metric_No_1_Dividend) AS Metric_1_Dividend_C_New
    FROM ' + @issue_table + N'
    WHERE PROJECT IS NOT NULL
    AND ISSUE_CREATION_DATE >= @StartDate 
    AND ISSUE_CREATION_DATE <= @EndDate
    GROUP BY ISSUE_CREATION_DATE, ISSUE_ID,SERVICE,SERVICE_GROUP,PORTFOLIO
    ) A
    GROUP BY SERVICE,Service_Group,Portfolio,Interval 
    '
    exec sys.sp_executesql @query,
        N'@StartDate date, @EndDate date, @Interval_2 VARCHAR(50)',
        @StartDate, @EndDate, @Interval_2;

    SET @index_first = @index_first + 1;
END

-- 合并所有中间表到最终表
DECLARE @unionQuery nvarchar(max) = N''

SELECT @unionQuery = @unionQuery + CASE WHEN @unionQuery <> '' THEN N' UNION ALL ' ELSE N'' END + 
                     N'SELECT SERVICE, Service_Group, Portfolio, Interval, Metric_1_Dividend_C_New FROM dbo.METRIC_1_' + Interval
FROM @Interval_List

SET @unionQuery = N'SELECT * INTO dbo.METRIC_1_All FROM (' + @unionQuery + N') AS CombinedData'

EXEC sys.sp_executesql @unionQuery

关键改动说明 #

• 优化日期判断：原脚本用FORMAT转换字符串比较，改为直接用日期类型比较，提升性能避免错误
• 添加合并逻辑：通过遍历时间段列表，动态生成UNION ALL语句，将所有中间表数据合并到dbo.METRIC_1_All
• 清理冗余变量：移除未使用的@CurrentDate和@service_table

方式二：直接生成合并表（推荐） #

无需创建中间表，直接拼接各时间段的查询逻辑，一次性生成合并后的最终表，减少资源消耗：

DECLARE @Interval_List as TABLE (index_1 int, Interval VARCHAR(50), Interval_2 VARCHAR(50), From_date date, To_date date)
INSERT INTO @Interval_List VALUES (1,  '2021_Q3',          '2021 Q3',        '2021-07-01', '2021-09-30')
INSERT INTO @Interval_List VALUES (2,  '2021_Q4',          '2021 Q4',        '2021-10-01', '2021-12-31')
INSERT INTO @Interval_List VALUES (3,  '2021_H2',          '2021 H2',        '2021-07-01', '2021-12-31')
INSERT INTO @Interval_List VALUES (4,  '2021',             '2021',           '2021-07-01', '2021-12-31')

DECLARE @issue_table nvarchar(max) = 'dbo.table1'
DECLARE @combinedQuery nvarchar(max) = N''
DECLARE @interval VARCHAR(50), @interval_2 VARCHAR(50), @startDate date, @endDate date

IF OBJECT_ID(@issue_table) IS NULL
BEGIN
    RAISERROR('无效对象！', 11, 1);
    RETURN;
END

-- 遍历时间段，拼接每个区间的查询语句
DECLARE intervalCursor CURSOR FOR
SELECT Interval, Interval_2, From_date, To_date FROM @Interval_List

OPEN intervalCursor
FETCH NEXT FROM intervalCursor INTO @interval, @interval_2, @startDate, @endDate

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @segmentQuery nvarchar(max) = N'
    SELECT 
    A.SERVICE,
    A.Service_Group,
    A.Portfolio,
    A.Interval,
    SUM(A.Metric_1_Dividend_C_New) AS Metric_1_Dividend_C_New
    FROM
    (
    SELECT 
    SERVICE,
    SERVICE_GROUP AS Service_Group,
    PORTFOLIO AS Portfolio,
    ''' + @interval_2 + ''' AS Interval,
    AVG(Metric_No_1_Dividend) AS Metric_1_Dividend_C_New
    FROM ' + @issue_table + N'
    WHERE PROJECT IS NOT NULL
    AND ISSUE_CREATION_DATE >= ''' + CONVERT(varchar, @startDate, 23) + '''
    AND ISSUE_CREATION_DATE <= ''' + CONVERT(varchar, @endDate, 23) + '''
    GROUP BY ISSUE_CREATION_DATE, ISSUE_ID,SERVICE,SERVICE_GROUP,PORTFOLIO
    ) A
    GROUP BY SERVICE,Service_Group,Portfolio,Interval'

    -- 拼接UNION ALL（第一个语句前不加）
    IF @combinedQuery <> N''
        SET @combinedQuery = @combinedQuery + N' UNION ALL ' + @segmentQuery
    ELSE
        SET @combinedQuery = @segmentQuery

    FETCH NEXT FROM intervalCursor INTO @interval, @interval_2, @startDate, @endDate
END

CLOSE intervalCursor
DEALLOCATE intervalCursor

-- 生成最终合并表
SET @combinedQuery = N'SELECT * INTO dbo.METRIC_1_All FROM (' + @combinedQuery + N') AS CombinedData'

EXEC sys.sp_executesql @combinedQuery

优势说明 #

• 无需创建多个中间表，减少磁盘IO和对象管理成本
• 同样优化了日期条件写法，性能更优
• 逻辑集中，避免多次执行独立查询的开销

内容的提问来源于stack exchange，提问作者Cevat Erkibaş