SQLTeam.com | Weblogs | Forums

Using a Cursor Variable to perfom a query and return the value from multiple tables

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

tablename

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.

tablecursorerror

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

one idea is dynamic sql

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