编辑以更好地阐明。
我创建了一个包含两列和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
1条答案
按热度按时间0s0u357o1#
如果我能加上
这样就可以给予您正在执行的动态SQL,这样就很容易进行故障排除。
高温加热