September 26, 2022 at 10:36 pm
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.
September 26, 2022 at 10:58 pm
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