Match Columns in Table A to Row Values in Table B to update another Column in Table B

I want to match columns of TableA to values in one column of TableB, in order to update values in another column in TableB.

TableA:

001100	001200	003000	004000	005000
1	0	1	1	0

TableB:

Column_1	Score
001100
001200
003000
004000
005000

So, for example, I want the “Score” column in TableB to be updated with value “1” for 001100. Hence the result table should look like:

Column_1	Score
001100	1
001200	0
003000	1
004000	1
005000	0

I know how to update this through hard-coding and one row at a time:

Update [TableB]
Set Score = [001100]
From  [Table A]
Where [Column_1] = '001100'

But I have over 50 such columns, so it would be great if someone could suggest how I can get this done without having to hard-code the column names. So a code that will dynamically fetch the names of the columns from TableA and then match it with tableB Column_1 values.

Answer

Here you go.

This will dynamically generate the correct SQL to update the Score in Table B from the values in Table A for any number of columns.

The only requrement is the names of the columns in Table A match the values of Column_1 in table B

First I need a list of the columns, this can be obtained from sys.columns. I then concatenated them into a string using for xml path.

Then I’m able to dynamically build an update statement that uses the column list and unpivots them to match the rows in TableB

Finally execute the dynamically built SQL.

Provided you have suitable indexes on the (actual) tables, this will be highly performant.

create table TableA ([001100] int, [001200] int, [003000] int, [004000] int [005000] int)

insert into TableA
select 1,0,1,1,0

create table TableB (Column_1 varchar(10), Score int)

insert into TableB (Column_1) select '001100'   
insert into TableB (Column_1) select '001200'   
insert into TableB (Column_1) select '003000'   
insert into TableB (Column_1) select '004000'   
insert into TableB (Column_1) select '005000'



declare @cols nvarchar(max), @sql nvarchar(max)

select @cols=Stuff((
        select ', '+QuoteName(c.name )
        from sys.tables t join sys.columns c on c.object_id=t.object_id
        where t.[name]='TableA'
        for xml path(''), type
    ).value('.[1]', 'varchar(100)'), 1, 2, ''
)

set @sql='
update b set
    b.score=p.val
from (
    select ' + @cols + '
    from TableA -- where clause if relevant
) s
unpivot (
 val for col in (' + @cols + ')
) p
join TableB b on b.Column_1=p.col'

exec(@sql)