526互联

当表数据很大时用临时表优化 INSERT INTO EXEC

发布时间 2023-09-08 15:58:39作者: 方寸山学习


CREATE TABLE #TmepHourDataDataItemChartData(
[ProjectID] [int] NOT NULL,
[DeviceID] [int] NOT NULL,
[DataItemID] [int] NOT NULL,
[CollectData] [decimal](18, 4) NULL,
[MaxData] [decimal](18, 4) NULL,
[MinData] [decimal](18, 4) NULL,
[AvgData] [decimal](18, 4) NULL,
[DiffData] [decimal](18, 4) NULL,
[CollectTime] [datetime] NOT NULL,

)
INSERT INTO #TmepHourDataDataItemChartData([ProjectID],[DeviceID],[DataItemID],[CollectData] ,[MaxData],[MinData] ,[AvgData],[DiffData],[CollectTime])
EXEC('select [ProjectID],[DeviceID],[DataItemID],[CollectData] ,[MaxData],[MinData] ,[AvgData],[DiffData],[CollectTime] from['+@InDBName+'].[dbo].['+@InTName+'] where ProjectID='+@ProjectID+' and DeviceID='+@DeviceID+' AND CollectTime>='''+@StartDate+''' AND CollectTime<'''+@EndDate+'''')

PRINT @InDBName+'].[dbo].['+@InTName

 

select * from #TmepHourDataDataItemChartData