I have a table with TABLE_NAME and ROW_COUNT, this has around 200+ table names populated. I want to be able to find the MAX (LAST_UPDATED) value from each table displayed, so my output will then have TABLE_NAME,ROW_COUNT,LAST_UPDATED
I have attempted some SQL Cursor, but as i've never used this before, i'm finding it difficult to use the variable to perform a query to return the data i want.
i know this is not correct but was my best attempt
DECLARE @TableName as VARCHAR(256)
DECLARE @updated_timestamp as DATETIME
DECLARE tablenamefromcursor CURSOR FOR
SELECT
OPEN @tablenamefromcursor
FETCH NEXT FROM tablenamefromcursor INTO @TableName, @RowCount
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE #tmpRowCount
SET updated_timestamp = (SELECT MAX(updated_timestamp) FROM @TableName)
FETCH NEXT FROM @tablenamefromcursor INTO @TableName, @RowCount
END
CLOSE
It's somewhat hard to tell exactly what you have, but something like this:
DECLARE @sql nvarchar(max)
DECLARE @TableName as VARCHAR(256)
DECLARE @updated_timestamp as DATETIME
DECLARE tablenamefromcursor CURSOR FOR
SELECT TableName, RowCount
FROM #tmpRowCount
OPEN tablenamefromcursor
FETCH NEXT FROM @tablenamefromcursor INTO @TableName, @RowCount
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'UPDATE #tmpRowCount SET updated_timestamp = ' +
'(SELECT MAX(updated_timestamp) FROM ' + @TableName + ') ' +
' WHERE TableName = ''' + @TableName + ''''
EXEC(@sql)
FETCH NEXT FROM @tablenamefromcursor INTO @TableName, @RowCount
END
CLOSE tablenamefromcursor
DEALLOCATE tablenamefromcursor
hello, sorry if i want clear..
I have a table like
and i want to use that table name in a query and return the MAX(updated_timestamp) from all the tables in the list and write it as a new column in the temp table called updated_timestamp
You would need to add the column to the table first. Then try the code above:
ALTER TABLE #tmpRowCount ADD updated_timestamp datetime NULL;
Great, thank you for your help - seems its close, just an issure with the declaration.
carefully examine the code provided.
DECLARE @sql nvarchar(max)
DECLARE @TableName as VARCHAR(256)
DECLARE @updated_timestamp as DATETIME
DECLARE tablenamefromcursor CURSOR FOR
SELECT TableName, RowCount
FROM #tmpRowCount
OPEN tablenamefromcursor
FETCH NEXT FROM @tablenamefromcursor INTO @TableName, @RowCount
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'UPDATE #tmpRowCount SET updated_timestamp = ' +
'(SELECT MAX(updated_timestamp) FROM ' + @TableName + ') ' +
' WHERE TableName = ''' + @TableName + ''''
EXEC(@sql)
FETCH NEXT FROM @tablenamefromcursor INTO @TableName, @RowCount
END
CLOSE tablenamefromcursor
DEALLOCATE tablenamefromcursor
where do you think the issue lies?
1 Like
DECLARE @RowCount int;
DECLARE @sql nvarchar(max);
DECLARE @TableName as VARCHAR(256);
DECLARE @updated_timestamp as DATETIME;
DECLARE tablenamefromcursor CURSOR FOR
SELECT TableName, RowCount
FROM #tmpRowCount
OPEN tablenamefromcursor
FETCH NEXT FROM tablenamefromcursor INTO @TableName, @RowCount
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'UPDATE #tmpRowCount SET updated_timestamp = ' +
'(SELECT MAX(updated_timestamp) FROM [' + @TableName + ']) ' +
' WHERE TableName = ''' + @TableName + ''''
EXEC(@sql)
FETCH NEXT FROM tablenamefromcursor INTO @TableName, @RowCount
END
CLOSE tablenamefromcursor
DEALLOCATE tablenamefromcursor
Thank you for the updates - i have since made the amendments and no longer get the declare errors. and with some stepping through, seperated the schema name and table name as seperate variables.
however, i think i may of found a limit to an UPDATE statement due to the following error
An aggregate may not appear in the set list of an UPDATE statement
guessing i may have to use a sub-query
you could use INSERT statement into the temp table
