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....... '

Why do you need dynamic sql? Also does table B only contain one row? If not how do you know what row to update? — Martin Smith, Jul 05 '14 at 11:51
Similarly to what @Martin Smith said, does table A contain only one set of values? If not, how do you know which set of values of table A updates [which row of] table B? — Andriy M, Jul 06 '14 at 00:04
Martin, Table B would always contain ONE row with about 140 columns of which only columns whose name matches with ID in Table A need to be updated. — user3758593, Jul 16 '14 at 02:34
@AndriyM , entries in column ID of Table A match with the column names of Table B. — user3758593, Jul 16 '14 at 02:35
And no duplicates in `A.ID`, correct? You might want to add those pieces of information to your main post to make the problem clearer to everyone coming across the question. — Andriy M, Jul 16 '14 at 06:17

score 0 · Answer 1 · answered Jul 05 '14 at 12:33

0

INSERT INTO tb  
SELECT [1],[2],[3],[4],[5],[6],[7] FROM
(SELECT 
id,value
  from ta)as p
PIVOT
(AVG(value) FOR id IN([1],[2],[3],[4],[5],[6],[7])
 )as bah

Fiddle

answered Jul 05 '14 at 12:33

Mihai

26,325
7
66
81

Thanks @Mihai.I need to do an UPDATE. The logic provided wont work. – user3758593 Jul 16 '14 at 02:37

mehdi lotfi · Accepted Answer · 2014-07-16T03:41:52.363

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)

Thanks @mehdi, With this approach however, you are setting values to NULL. Apart from that I need to match the column names at run time and not hard code it. — user3758593, Jul 16 '14 at 02:39
I edit my post. If your want to have dynamic pivot use view my edit. — mehdi lotfi, Jul 16 '14 at 03:42

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

table A

table B

2 Answers2

Linked