Post reply

cursor to do update for multiple tables of a specific field

  • September 26, 2022 at 10:36 pm

    #4092208

    I am trying to scramble ID field in multiple tables in database.

    I created a cursor to first query the database which has studentid field. Then use a cursor with an update statement to update studentId field. But I got an error : Must declare the table variable "@myTableName". any help please? Thanks

    By the way I did a step before these to disable all the FK of all the tables so that studentid FK can be disabled and enable update, that is not included in below code, and that part runs OK

    DECLARE @myColumnName VARCHAR(50) -- column name
    DECLARE @myTableName VARCHAR(250) -- table name
    DECLARE db_cursor CURSOR FOR
    SELECT c.name AS ColumnName
    ,t.name AS TableName
    FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
    WHERE c.name LIKE '%studentid%'
    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @myColumnName, @myTableName

    WHILE @@FETCH_STATUS = 0
    BEGIN
    UPDATE a
    SET @myColumnName=b.studentidNew
    FROM @myTableName a
    JOIN SIS.dbo.vw_StudentIDMatch b  --view that has a mapping of new/old id.
    ON @myColumnName=b.studentIDOld
    FETCH NEXT FROM db_cursor INTO @myColumnName, @myTableName
    END
    CLOSE db_cursor
    DEALLOCATE db_cursor

    I also tried to use dynamic query:

    @sqlupdate=N' UPDATE a set ' + @myClumnName+'=b.studentidNew from ' +@myTableName +' a join
    SIS.dbo.vw_xcrStudentIDMatch b on a.'+@myColumnName +'=b.studentidOld'
    PRINT @sqlupdate

    but it gives syntax error.

    • This topic was modified 1 year, 7 months ago by  sqlfriend.
    • This topic was modified 1 year, 7 months ago by  sqlfriend.

  • September 26, 2022 at 10:58 pm

    #4092211

    I think for the second one - the dynamic query, I forgot to use a set key word.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply