循环遍历多数据库表并插入临时表的SQL实现求助

我有一个含2列、约60行的SiteDboTable，生成语句如下：

----SiteDboTable---
SELECT 
    [SiteName]
    , CASE WHEN [Type] = 'Wind' THEN CONCAT('[YYY].[dbo].', '[', Replace(Translate([SiteName], ' -\','???'),'?',''), 'Turbine]')
        ELSE  CONCAT('[YYY].[dbo].', '[', Replace(Translate([SiteName], ' -\','???'),'?',''), 'Inverter]') 
        END AS dboName
FROM [XXX].[dbo].[Site]
Order By SiteName

该表输出示例：

SiteName  dboName
site1     [YYY].[dbo].[site1Inverter] 
....      .....
site4     [YYY].[dbo].[site4Inverter]
..n..     ..n..

需要遍历SiteDboTable的每一行，将数据插入到HLEEtmp_table中：把SiteName和dboName分别替换目标查询中的@SiteName和@TableName，每次执行查询后将结果存入目标表。目标查询模板：

---HLEEtmp_table---
SELECT 
    dateadd(hour, datediff(hour, 0, DataTimeStamp), 0) AS DataTimeStamp
    ,@SiteName AS Site 
    , DeviceID
    , AVG([RealPowerAC]) AS RealPowerAC_MEAN
FROM @TableName 
WHERE DataTimeStamp >= DATEADD(day,-30,GETDATE())
GROUP BY dateadd(hour, datediff(hour, 0, DataTimeStamp), 0)
    , datepart(hour,DataTimeStamp)
    , [DeviceID];

尝试的WHILE循环动态SQL存在多处错误：

• @TableName变量未赋值，导致动态SQL中FROM子句为空
• 删除已处理行时，WHERE dboName = @TableName因变量未赋值无法生效
• 最后更新循环计数时，错误将计数赋值给@SiteName变量
• 每次循环创建临时表再插入，效率冗余

以下提供三种可行方案，适配不同场景需求：

方案1：修复WHILE循环逻辑 #

-- 清理并创建临时表
IF (OBJECT_ID('tempdb..#HLEEtmp_table') IS NOT NULL )
 DROP TABLE #HLEEtmp_table;
IF (OBJECT_ID('tempdb..#SiteDboTable') IS NOT NULL )
 DROP TABLE #SiteDboTable;

-- 优化临时表字段类型（避免字符串存储日期/数值的问题）
CREATE TABLE #HLEEtmp_table (
    DataTimeStamp DATETIME,
    Site VARCHAR(50),
    DeviceID VARCHAR(50),
    RealPowerAC_MEAN DECIMAL(18,2)
)

-- 生成SiteDboTable
SELECT 
    [SiteName]
    , CASE WHEN [Type] = 'Wind' THEN CONCAT('[XXX].[dbo].', '[', Replace(Translate([SiteName], ' -\','???'),'?',''), 'Turbine]')
        ELSE  CONCAT('[XXX].[dbo].', '[', Replace(Translate([SiteName], ' -\','???'),'?',''), 'Inverter]') 
        END AS dboName
INTO #SiteDboTable
FROM [YYY].[dbo].[Site]
Order By SiteName

-- 修正循环逻辑
DECLARE @TableCount INT 
DECLARE @CurrentSiteName VARCHAR(50) 
DECLARE @CurrentTableName VARCHAR(256)
DECLARE @SQL NVARCHAR(MAX) -- 使用NVARCHAR支持Unicode字符

SELECT @TableCount = COUNT(1) FROM #SiteDboTable

WHILE @TableCount > 0 
BEGIN
    -- 获取当前行的SiteName和dboName
    SELECT TOP 1 
        @CurrentSiteName = SiteName,
        @CurrentTableName = dboName
    FROM #SiteDboTable 
    ORDER BY SiteName

    -- 拼接动态SQL，注意SiteName需加单引号作为字符串常量
    SET @SQL = N'
        INSERT INTO #HLEEtmp_table
        SELECT 
            DATEADD(HOUR, DATEDIFF(HOUR, 0, DataTimeStamp), 0) AS DataTimeStamp
            , ''' + @CurrentSiteName + ''' AS Site 
            , DeviceID
            , AVG([RealPowerAC]) AS RealPowerAC_MEAN
        FROM ' + @CurrentTableName + ' 
        WHERE DataTimeStamp >= DATEADD(DAY,-30,GETDATE())
        GROUP BY 
            DATEADD(HOUR, DATEDIFF(HOUR, 0, DataTimeStamp), 0)
            , DATEPART(HOUR,DataTimeStamp)
            , [DeviceID];'
    
    EXEC sp_executesql @SQL -- 用sp_executesql比EXEC更安全

    -- 删除已处理的行
    DELETE FROM #SiteDboTable 
    WHERE SiteName = @CurrentSiteName AND dboName = @CurrentTableName

    -- 更新循环计数
    SELECT @TableCount = COUNT(1) FROM #SiteDboTable
END

方案2：使用游标（逻辑更清晰） #

游标适合小批量数据遍历，代码可读性更强：

-- 临时表创建部分同方案1，省略

DECLARE site_cursor CURSOR FOR
SELECT SiteName, dboName
FROM #SiteDboTable
ORDER BY SiteName

DECLARE @CurrentSiteName VARCHAR(50), @CurrentTableName VARCHAR(256)
DECLARE @SQL NVARCHAR(MAX)

OPEN site_cursor
FETCH NEXT FROM site_cursor INTO @CurrentSiteName, @CurrentTableName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = N'
        INSERT INTO #HLEEtmp_table
        SELECT 
            DATEADD(HOUR, DATEDIFF(HOUR, 0, DataTimeStamp), 0) AS DataTimeStamp
            , ''' + @CurrentSiteName + ''' AS Site 
            , DeviceID
            , AVG([RealPowerAC]) AS RealPowerAC_MEAN
        FROM ' + @CurrentTableName + ' 
        WHERE DataTimeStamp >= DATEADD(DAY,-30,GETDATE())
        GROUP BY 
            DATEADD(HOUR, DATEDIFF(HOUR, 0, DataTimeStamp), 0)
            , DATEPART(HOUR,DataTimeStamp)
            , [DeviceID];'
    
    EXEC sp_executesql @SQL

    FETCH NEXT FROM site_cursor INTO @CurrentSiteName, @CurrentTableName
END

CLOSE site_cursor
DEALLOCATE site_cursor

方案3：拼接所有SQL一次性执行（最优效率） #

将所有动态SQL拼接成一个语句一次性执行，减少多次执行的开销：

-- 临时表创建部分同方案1，省略

DECLARE @FullSQL NVARCHAR(MAX) = N''

SELECT @FullSQL = @FullSQL + N'
    INSERT INTO #HLEEtmp_table
    SELECT 
        DATEADD(HOUR, DATEDIFF(HOUR, 0, DataTimeStamp), 0) AS DataTimeStamp
        , ''' + SiteName + ''' AS Site 
        , DeviceID
        , AVG([RealPowerAC]) AS RealPowerAC_MEAN
    FROM ' + dboName + ' 
    WHERE DataTimeStamp >= DATEADD(DAY,-30,GETDATE())
    GROUP BY 
        DATEADD(HOUR, DATEDIFF(HOUR, 0, DataTimeStamp), 0)
        , DATEPART(HOUR,DataTimeStamp)
        , [DeviceID];'
FROM #SiteDboTable

EXEC sp_executesql @FullSQL

• 字段类型优化：原临时表用VARCHAR存储日期和数值，建议改为DATETIME和DECIMAL，避免数据失真和转换问题
• SQL注入防护：如果SiteName可能包含单引号等特殊字符，需用REPLACE(@CurrentSiteName, '''', '''''')转义
• 性能选择：60行数据量极小，三种方案均可行，方案3的一次性执行效率最高
• 变量类型：动态SQL变量建议用NVARCHAR(MAX)，支持Unicode字符

内容的提问来源于stack exchange，提问作者BB.squared