To expand on Gus solution:

(please note I added employee_code to the internal table variable since I think it might be helpful to know what row the change refers to 😉 )

DECLARE @MyTableVar table(

employee_code int NOT NULL,

Date_of_change DATETIME NOT NULL,

firstname_new VARCHAR(20),

firstname_old VARCHAR(20),

surname_new VARCHAR(20),

surname_old VARCHAR(20)

);

UPDATE #Payroll_employees

SET

firstname = HR.firstname,

surname = HR.surname

OUTPUT

INSERTED.employee_code,

GETDATE(),

INSERTED.firstname,

DELETED.firstname,

INSERTED.surname,

DELETED.surname

INTO @MyTableVar

FROM #HR_employees AS HR

INNER JOIN #Payroll_employees AS Payroll ON Payroll.employee_code = HR.employee_code

WHERE

(

Payroll.firstname != HR.firstname

OR Payroll.surname != HR.surname

)

INSERT INTO #log (Date_of_change, Field, OldValue, NewValue)

SELECT

--employee_code,

Date_of_change,

ColName AS ColumnName,

CASE WHEN ColName = 'Firstname' AND firstname_old != firstname_new

THEN firstname_old

ELSE surname_old

END AS OldValue,

CASE WHEN ColName = 'Firstname' AND firstname_old != firstname_new

THEN firstname_new

ELSE surname_new

END AS OldValue

FROM @MyTableVar MTV

CROSS APPLY

(

SELECT 'Firstname' AS ColName UNION ALL

SELECT 'Surname'

)sub

WHERE

(

ColName = 'Firstname' AND firstname_old != firstname_new

OR

ColName = 'Surname' AND surname_old != surname_new

)