是填充临时表的更有效方法 [英] Is the a more efficient way to populate a temp table

问题描述

下面是超过900个相同插入语句的前5个插入（只是表格更改名称），字段  MODIFIEDAT存在于所有表格中，并希望分析最近2天的计数。显然下面如果重复900+
时间可能不是正确的方法。想知道猫是否有另一种方式。

Hi, below is the first 5 inserts of over 900 identical insert statement (just table changes name), the field MODIFIEDAT exists on ALL tables and wanted to analyse the counts for the last 2 days across the board. Obviously below if repeated 900+ time is probably not the correct way. Wondered if another way the skin the cat.

;DECLARE @BUILDMODIFIED TABLE 
(Tablename varchar(50), MODIFIEDAT date, RowCnt int)

INSERT INTO @BUILDMODIFIED SELECT'LZO_ACTUALPRODUCT', CONVERT(date, MODIFIEDAT) as MODAT, COUNT(*) FROM LZO_ACTUALPRODUCT WHERE MODIFIEDAT >= '06 Mar 2019' GROUP BY CONVERT(date, MODIFIEDAT)
INSERT INTO @BUILDMODIFIED SELECT'LZO_ACTUALPRODUCTPACK', CONVERT(date, MODIFIEDAT) as MODAT, COUNT(*) FROM LZO_ACTUALPRODUCTPACK WHERE MODIFIEDAT >= '06 Mar 2019' GROUP BY CONVERT(date, MODIFIEDAT)
INSERT INTO @BUILDMODIFIED SELECT'LZO_ADMININSTRUCTION', CONVERT(date, MODIFIEDAT) as MODAT, COUNT(*) FROM LZO_ADMININSTRUCTION WHERE MODIFIEDAT >= '06 Mar 2019' GROUP BY CONVERT(date, MODIFIEDAT)
INSERT INTO @BUILDMODIFIED SELECT'LZO_ADMINMETHOD', CONVERT(date, MODIFIEDAT) as MODAT, COUNT(*) FROM LZO_ADMINMETHOD WHERE MODIFIEDAT >= '06 Mar 2019' GROUP BY CONVERT(date, MODIFIEDAT)
INSERT INTO @BUILDMODIFIED SELECT'LZO_ADMISSIONDIETARYREQUIREMENT', CONVERT(date, MODIFIEDAT) as MODAT, COUNT(*) FROM LZO_ADMISSIONDIETARYREQUIREMENT WHERE MODIFIEDAT >= '06 Mar 2019' GROUP BY CONVERT(date, MODIFIEDAT)

推荐答案

您可以使用动态sql生成语句然后执行它

you can use dynamic sql to generate the statement and then execute it

喜欢

declare @InsertSQL varchar(max)


SET @InsertSQL = STUFF((SELECT CHAR(13) + CHAR(10) + 'INSERT INTO @BUILDMODIFIED SELECT''' + TABLE_NAME + ''', CONVERT(date, MODIFIEDAT) as MODAT, COUNT(*) FROM ' + TABLE_NAME + ' WHERE MODIFIEDAT >= ''06 Mar 2019'' GROUP BY CONVERT(date, MODIFIEDAT)'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME 
IN (
'LZO_ACTUALPRODUCT',
'LZO_ACTUALPRODUCTPACK',
'LZO_ADMININSTRUCTION',
...)
FOR XML PATH('')),1,2,'')

DECLARE @SQL varchar(max) = '
;DECLARE @BUILDMODIFIED TABLE 
(Tablename varchar(50), MODIFIEDAT date, RowCnt int) 
' + @InsertSQL + '
SELECT * FROM @BUILDMODIFIED'

EXEC(@SQL)

但是如果你希望表格在相同的范围内用于进一步的连接等

but if you want the table to be in same scope to use it for further joins etc

这篇关于是填充临时表的更有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！