SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table Person (
 StudentDBID varchar(8) primary key, 
 Forename varchar(30) not null, 
 PreferredForename varchar(30), 
 Surname varchar(30),
 UpdatedOn datetime2, 
 CreatedOn datetime2 not null default SYSDATETIME()
)
insert into Person 
 (StudentDBID, Forename, PreferredForename, Surname)
values
  ('J7201', 'John', null, 'Doe')
, ('B9701', 'Bob', 'Booby', 'Modest')
, ('J0402', 'Jane', null, null)
, ('M0803', 'Milke', null, 'Hammer')
4 rows affected

create table #tmpTable (
 STUD_ISN varchar(8) not null primary key, 
 STUD_Forename varchar(30) not null default '', 
 STUD_Forename_1 varchar(30) not null default '', 
 STUD_Surname varchar(30) not null default ''
)
insert into #tmpTable 
 (STUD_ISN, STUD_Forename, STUD_Forename_1, STUD_Surname) 
values
  ('B9701   ', 'Bob   ', 'Bobby  ', 'Modest  ')
, ('J0402   ', 'Jane  ', 'JD     ', 'Doe     ')
, ('M0803   ', 'Mike  ', '       ', 'Hammer  ')
, ('J0504   ', 'Jim   ', 'Jimmy  ', 'Savage  ')
4 rows affected

MERGE Person t
USING #tmpTable s 
   ON s.STUD_ISN = t.StudentDBID
WHEN NOT MATCHED THEN 
  INSERT (
    StudentDBID
  , Forename
  , PreferredForename
  , Surname
  )
  VALUES (
    NULLIF(TRIM(s.STUD_ISN), '')
  , NULLIF(TRIM(s.STUD_Forename), '')
  , NULLIF(TRIM(s.STUD_Forename_1), '')
  , NULLIF(TRIM(s.STUD_Surname), '')
  )
WHEN MATCHED
AND (
     COALESCE(t.Forename, '') != TRIM(s.STUD_Forename)
  OR COALESCE(t.PreferredForename, '') != TRIM(s.STUD_Forename_1)
  OR COALESCE(t.Surname, '') != TRIM(s.STUD_Surname)
) 
THEN 
UPDATE SET
  UpdatedOn = SYSDATETIME()
, Forename = NULLIF(TRIM(s.STUD_Forename), '')
, PreferredForename = NULLIF(TRIM(s.STUD_Forename_1), '')
, Surname = NULLIF(TRIM(s.STUD_Surname), '')
;
4 rows affected

SELECT *
FROM Person


StudentDBID
Forename
PreferredForename
Surname
UpdatedOn
CreatedOn


B9701
Bob
Bobby
Modest
2022-01-14 12:58:05.9862269
2022-01-14 12:58:05.9862269

J0402
Jane
JD
Doe
2022-01-14 12:58:05.9862269
2022-01-14 12:58:05.9862269

J0504
Jim
Jimmy
Savage
null
2022-01-14 12:58:05.9862269

J7201
John
null
Doe
null
2022-01-14 12:58:05.9862269

M0803
Mike
null
Hammer
2022-01-14 12:58:05.9862269
2022-01-14 12:58:05.9862269

StudentDBID	Forename	PreferredForename	Surname	UpdatedOn	CreatedOn
B9701	Bob	Bobby	Modest	2022-01-14 12:58:05.9862269	2022-01-14 12:58:05.9862269
J0402	Jane	JD	Doe	2022-01-14 12:58:05.9862269	2022-01-14 12:58:05.9862269
J0504	Jim	Jimmy	Savage	null	2022-01-14 12:58:05.9862269
J7201	John	null	Doe	null	2022-01-14 12:58:05.9862269
M0803	Mike	null	Hammer	2022-01-14 12:58:05.9862269	2022-01-14 12:58:05.9862269