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),
  )

Kindly confirm whether it is working or not as i  was doing it in SQL fiddle


update userimport
set
userid =a.userid,
firstname=a.firstname,
lastname=a.lastname,
middlename=a.middlename,
udf1= case when b.fieldno=1 then [value] end ,
udf2= case when b.fieldno=2 then [value] end
from
users a inner join udf b on a.userid=b.userid