sql server - updating values in one table from another table using DYNAMIC SQL in MSSQL

Question

I have a temp table A having 2 columns col1: ID col2: Value generated using XML. I need to update the columns in table B corresponding to column1:ID of table A with values present in col2: Value of table A. NOTE: Only specific columns and not all in table B need to be updated

table A

+----+-------+
| ID | Value |
+----+-------+
|  1 |   533 |
|  5 |    34 |
|  6 |    56 |
+----+-------+

table B

+-----+---+---+---+----+----+---+
|  1  | 2 | 3 | 4 | 5  | 6  | 7 |
+-----+---+---+---+----+----+---+
| 533 |   |   |   | 34 | 56 |   |
+-----+---+---+---+----+----+---+

---

 declare dynsql varchar(4000) = ' update table B set....... '

Answer 1

Try following query:

UPDATE TableB
SET [1] = ISNULL(z.[1],TableB.[1]),
    [2] = ISNULL(z.[2],TableB.[2]),
    [3] = ISNULL(z.[3],TableB.[3]),
    [4] = ISNULL(z.[4],TableB.[4]),
    [5] = ISNULL(z.[5],TableB.[5]),
    [6] = ISNULL(z.[6],TableB.[6]),
    [7] = ISNULL(z.[7],TableB.[7])
FROM (
    SELECT [1],[2],[3],[4],[5],[6],[7] 
    FROM (SELECT Id, Value
          FROM TableA)AS p
    PIVOT (MAX(Value) FOR Id IN([1],[2],[3],[4],[5],[6],[7]))AS pvt
    )z

EDIT

In order to have dynamic pivot use following query:

DECLARE @columns1 NVARCHAR(1000) = '',
        @columns2 NVARCHAR(1000) = '',
        @sql NVARCHAR(MAX)

SELECT @Columns1 = STUFF((SELECT ',['+Value+'] = ISNULL(z.['+Value+'],TableB.['+Value+'])'
                        FROM (SELECT DISTINCT Value FROM TableA)z
                        FOR XML PATH('')),1,1,''),
        @Columns2 = STUFF((SELECT ',['+Value+']'
                        FROM (SELECT DISTINCT Value FROM TableA)z
                        FOR XML PATH('')),1,1,'')


SET @sql = 'Update TableB
            Set '+@columns1+' 
            From ( 
                  Select '+ @columns2+'
                  From (Select Id, Value From TableA) AS p
                  Pivot (MAX(Value) For Id IN ('+@columns2+')) AS Pvt
            )z'

EXECUTE(@sql)