what i have done is to convert the records in userimport from columns as rows first.
ie (udf1, udf2, ...) is converted as
userid,fieldno,value
1 , 1 , New1
1 , 2 , New2
1 , 3 , New3
1 , 4 , New4
1 , 5 , New5
So the result of that step would be how we want the final output to look like.
This is followed by a update using join with the result set in step 1 with the udf table on the keys userid,fieldno.
(You could also accomplish the same using MERGE statement as well)
create table users
(
userid int,
firstname varchar(25),
lastname varchar(25),
middlename varchar(25)
);
insert into users values (1,'some','dude','big');
create table udf(userid int,fieldno int, value varchar(25));
insert into udf values (1,1,'123456');
insert into udf values (1,2,'text1');
create table userimport
(
userid int
,firstname varchar(25)
,lastname varchar(25)
,middlename varchar(25)
,udf1 varchar(25)
,udf2 varchar(25)
,udf3 varchar(25)
,udf4 varchar(25)
,udf5 varchar(25)
);
insert into userimport values(1,'some','dude','big','New1','New2','New3','New4','New5');
update a2
set a2.value=b2.value1
from udf a2
join (select a.userid
,b.rnk as fieldno
,case when b.rnk=1 then a.udf1
when b.rnk=2 then a.udf2
when b.rnk=3 then a.udf3
when b.rnk=4 then a.udf4
when b.rnk=5 then a.udf5
end as Value1
from userimport a
join (select top 5 row_number() over(order by (select null)) as rnk
from information_schema.tables
)b
on 1=1
)b2
on a2.userid=b2.userid
and a2.fieldno=b2.fieldno;
select *
from udf;