如何借助Template表循环生成动态查询，关联多表创建公司测试表？

如何通过遍历Template表的每行记录，利用动态查询实现跨表关联，并为每个Schema（如Company1、Company2）创建对应的数据表？

Template表结构及数据如下：

需求是遍历每个唯一的Schema，生成关联对应Schema下Year和Quarter表的查询，自动创建以Schema名称+Test命名的数据表，同时生成字段匹配校验的结果列。

可以通过动态SQL结合对Template表的分组聚合，自动生成并执行每个Schema对应的关联查询。以下是具体实现步骤及代码：

动态SQL实现（以SQL Server为例） #

DECLARE @SchemaName NVARCHAR(100)
DECLARE @DynamicSQL NVARCHAR(MAX)
DECLARE @SelectColumns NVARCHAR(MAX)
DECLARE @JoinCondition NVARCHAR(MAX)

-- 声明游标遍历所有唯一的Schema
DECLARE SchemaCursor CURSOR FOR
SELECT DISTINCT [Schema] FROM Template

OPEN SchemaCursor
FETCH NEXT FROM SchemaCursor INTO @SchemaName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 拼接SELECT子句：包含字段和字段匹配校验列
    SELECT @SelectColumns = STRING_AGG(
        CASE 
            WHEN t.ColumnsInYearTables = 'ID' 
                THEN 'a.' + QUOTENAME(t.ColumnsInYearTables)
            ELSE 'a.' + QUOTENAME(t.ColumnsInYearTables) + ', b.' + QUOTENAME(t.ColumnsInQuarterTables) + ', ' +
                 'CASE WHEN a.' + QUOTENAME(t.ColumnsInYearTables) + ' = b.' + QUOTENAME(t.ColumnsInQuarterTables) + 
                 ' THEN ''TRUE'' ELSE ''FALSE'' END AS ' + QUOTENAME('Test' + CAST(ROW_NUMBER() OVER(PARTITION BY t.[Schema] ORDER BY t.ID) AS NVARCHAR))
        END, ', '
    )
    FROM Template t
    WHERE t.[Schema] = @SchemaName
    ORDER BY t.ID

    -- 提取ID字段的关联条件
    SELECT @JoinCondition = 'a.' + QUOTENAME(t.ColumnsInYearTables) + ' = b.' + QUOTENAME(t.ColumnsInQuarterTables)
    FROM Template t
    WHERE t.[Schema] = @SchemaName AND t.ColumnsInYearTables = 'ID'

    -- 构建完整的动态SQL语句
    SET @DynamicSQL = N'
    SELECT * 
    INTO ' + QUOTENAME(@SchemaName + 'Test') + '
    FROM (
        SELECT ' + @SelectColumns + '
        FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME((SELECT TOP 1 YearTablesNames FROM Template WHERE [Schema] = @SchemaName)) + ' a
        JOIN ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME((SELECT TOP 1 QuarterTableName FROM Template WHERE [Schema] = @SchemaName)) + ' b 
            ON ' + @JoinCondition + '
    ) f'

    -- 执行动态SQL
    EXEC sp_executesql @DynamicSQL

    FETCH NEXT FROM SchemaCursor INTO @SchemaName
END

CLOSE SchemaCursor
DEALLOCATE SchemaCursor

执行效果示例 #

Company1Test表 #

Company2Test表 #

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