[Solved] updating values in one table from another table using DYNAMIC SQL in MSSQL

by


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)

2

solved updating values in one table from another table using DYNAMIC SQL in MSSQL