通过合并/跟踪多个表中的已更新记录来更新SQL
通过合并/跟踪多个表中的已更新记录来更新SQL
提问于 2019-03-25 21:50:50
为了好玩，我决定编写代码，列出今天更新的所有表和记录数(使用SQL Server)。例如：
TableName ModifiedToday
table1 0
table2 5
table3 2第一步是创建一个临时表，其中包含数据库中包含date_modified字段的所有表名：
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp
SELECT
t.name AS 'TableName'
,-1 AS 'ModifiedToday'
into #temp
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%date_modified%'
ORDER BY TableName接下来，我尝试根据临时表的内容对临时表执行更新。但是，我不太熟悉SQL中的合并或循环，并且很难使用表名进行更新。有什么想法吗？
DECLARE @tname varchar(200) = ''
WHILE ( SELECT COUNT(*) FROM #temp WHERE ModifiedToday = -1) >0
BEGIN
UPDATE #temp
SET @tname = TableName,
ModifiedToday = (COALESCE(CHAR(500), '') + 'SELECT COUNT(*) FROM '+ @tname + ' WHERE DATEDIFF(day,GETDATE(),isnull(date_modified,0)) = 0')
WHERE @tname = TableName
BREAK
ENDStack Overflow用户
回答已采纳
发布于 2019-03-25 22:43:51
看看这是否适合您-您可以将表名列表填充到临时表的SYSNAME列中，将表名列表插入到表变量中，然后遍历表变量并执行一些动态sql以从每个表中获取计数。
DECLARE @SQL NVARCHAR(MAX)
CREATE TABLE #temp ( TableName SYSNAME, ModifiedToday INT )
INSERT INTO #temp ( TableName, ModifiedToday )
SELECT
t.name AS 'TableName'
,-1 AS 'ModifiedToday'
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name = 'date_modified' --changed this to '=' instead of 'LIKE' since you have the column name hard coded in the query below
DECLARE @SQLObjectList TABLE ( SQLObject sysname)
INSERT INTO @SQLObjectList( SQLObject) SELECT DISTINCT TableName FROM #temp WHERE ModifiedToday = -1
DECLARE @xSQLObject VARCHAR(1000)
WHILE EXISTS (SELECT 1 FROM @SQLObjectList)
BEGIN
SELECT TOP 1 @xSQLObject = SQLObject FROM @SQLObjectList
SET @SQL = 'UPDATE #temp SET ModifiedToday =
( SELECT COUNT(*) FROM '+@xSQLObject+' WHERE DATEDIFF(day,GETDATE(),isnull(date_modified,0)) = 0 )
WHERE TableName = '''+@xSQLObject+''' '
EXECUTE sp_executesql @SQL
DELETE FROM @SQLObjectList WHERE SQLObject = @xSQLObject
END
SELECT * FROM #temp;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/55339356
复制相关文章
相似问题
腾讯云开发者
