编辑以更好地阐明。
我创建了一个包含两列和60行的表

----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
使用上面的SiteDboTable表，我想使用两列（SiteName，dboName）作为变量。每个变量都将在下面的脚本中更新/更改。因此，当我运行HLEEtmp_table时，它每次都会更改SiteName和FROM语句。然后，“"HLEEtmp_table”“迭代的每次迭代的数据，该数据将被输入到另一个表中以存储所有数据。

---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];'

下面是我正在尝试的。不确定这是否是正确的方法。我认为光标方法可能不起作用。
任何想法或帮助将不胜感激。
首先，我创建了两个临时表，一个是INSERT数据表所需的临时表，另一个是包含FROM语句模式的表。

IF (OBJECT_ID('tempdb..#HLEEtmp_table') IS NOT NULL )
 DROP TABLE #HLEEtmp_table;
IF (OBJECT_ID('tempdb..#SiteDboTable') IS NOT NULL )
 DROP TABLE #SiteDboTable;
IF (OBJECT_ID('tempdb..#HLEEtmp_table2') IS NOT NULL )
 DROP TABLE #HLEEtmp_table2;
 
--Create Temp Table #HLEEtmp_table; this table will hold the data I need---------
CREATE TABLE #HLEEtmp_table (
    DataTimeStamp VARCHAR(50),
    Site VARCHAR(50),
    DeviceID VARCHAR(50),
    RealPowerAC_MEAN VARCHAR(50)    
)
----------------------------------------------------------
/*Create Temp Table #SiteDboTable: 
    - This table will list of all [DB].[dbo].[table] names in column [AssetName]
    - #SiteDboTable will create a table with 60 rows and 2 columns.
    - From #SiteDboTable: need [Site] column and [dboName] column for insert into #HLEEtmp_table 
    - #SiteDboTable.[Site] = #HLEEtmp_table.[Site]
    - #SiteDboTable.[dboName] will be #HLEEtmp_table FROM statement: FROM #SiteDboTable.[dboName] "i.e.  [XXX].[dbo].[AVInverter]"*/

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

对于临时表#SiteDboTable中的数据，我希望使用下表#HLEEtmp_table2中的#SiteDboTable列。将数据从#HLEEtmp_table2和INSERT拉入临时数据表#HLEEtmp_table。

---------------------------------
--INSERT INTO #HLEEtmp_table VALUES ('DataTimeStamp','Site','DeviceID','RealPowerAC_MEAN')

DECLARE @TableCount int 
DECLARE @SiteName varchar(50) 
DECLARE @SQL varchar(max)
DECLARE @TableName varchar(256)

SELECT @TableCount = COUNT(1) from #SiteDboTable

WHILE @TableCount > 0 
BEGIN

    SELECT TOP 1 @SiteName = SiteName from #SiteDboTable ORDER BY SiteName
    SELECT @SQL = '
                    SELECT 
                      dateadd(hour, datediff(hour, 0, DataTimeStamp), 0) AS DataTimeStamp
                      ,'+@SiteName+' AS Site 
                      , DeviceID
                      , AVG([RealPowerAC]) AS RealPowerAC_MEAN
                    INTO #HLEEtmp_table2
                    FROM
                         '+@TableName+'
                    WHERE
                      DataTimeStamp >= DATEADD(day,-30,GETDATE())
                    GROUP BY
                      dateadd(hour, datediff(hour, 0, DataTimeStamp), 0)
                      , datepart(hour,DataTimeStamp)
                      , [DeviceID];
                      
                    -- Drop the Primary Key Column from the temp table  
                    -- Alter Table #HLEEtmp_table2 Drop Column [PrimaryKeyColumn]

                    -- Insert that into your other big table
                    Insert Into #HLEEtmp_table
                        Select * From #HLEEtmp_table2

                    -- Drop the temp table you created
                    Drop Table #HLEEtmp_table2
                    '
    
    EXEC (@SQL)
    
    DELETE #SiteDboTable WHERE dboName = @TableName
    SELECT @SiteName = COUNT(1) from #SiteDboTable
    
END