Table t1 has primary key as NUMBER and we have to set the attributes A, B, C, ... using the rows of Table t2, hence using PIVOT.

This query is taking more than 4 hours to execute.

Is there a way to improve this?

DECLARE @number [varchar](40)

    DECLARE cursor CURSOR FOR 
    SELECT DISTINCT(t1.NUMBER) 
    FROM [TABLE1] t1
    INNER JOIN [TABLE2] t2
    ON t1.NUMBER = t2.NUMBER

    OPEN cursor

    BEGIN TRANSACTION
    FETCH NEXT FROM cursor INTO @number

    WHILE(@@FETCH_STATUS = 0)
        BEGIN
        UPDATE t1
        SET A = piv.A,
            B= piv.B,
            C= piv.C, ...
        FROM
            (SELECT A, B, C, ... 
            FROM
                (SELECT VALUE, NAME
                FROM t2
                WHERE NUMBER = @number
                ) d
                PIVOT
                (MAX(VALUE)
                FOR NAME IN (A, B, C, ...
                ) p
            ) AS piv
        WHERE t1.NUMBER = @number

    FETCH NEXT FROM cursor INTO @number
    END
    COMMIT TRANSACTION

CodePudding user response：

Do it the SQL way.

Update from a Pivot.

UPDATE t1
SET A = t2.A
  , B = t2.B
  , C = t2.C
FROM TABLE1 t1
JOIN (
    SELECT *
    FROM (
      SELECT [NUMBER], NAME, [VALUE]
      FROM TABLE2
      WHERE NAME IN ('A','B','C')
    ) Src
    PIVOT (
      MAX([VALUE])
      FOR NAME IN ([A],[B],[C]) 
    ) Pvt
) t2 ON t2.[NUMBER] = t1.[NUMBER]

Before update

select * from TABLE1

After update

select * from TABLE1

Demo on db<>fiddle here