SQL Server 2022

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

CREATE TABLE [dbo].[contact_compare_fields] 
(
    [id]       int identity(1,1),
    [sf_field] varchar(100),
    [h_field]  varchar(100)
);

INSERT INTO [contact_compare_fields] 
VALUES
    ('Title',               'Job Title') /*,
    ('FirstName',           'First Name'),
    ('LastName',            'Last Name'),
    ('MailingStreet',       'Person Street'),
    ('MailingCity',         'Person City'),
    ('MailingState',        'Person State'),
    ('MailingPostalCode',   'Person Zip Code'),
    ('MailingCountry',      'Country'),
    ('Department',          'Department'),
    ('Email',               'Email Address'),
    ('Phone',               'Direct Phone Number'),
    ('Fax',                 'Fax'),
    ('MobilePhone',         'Mobile Phone'),
    ('Job_Level__c',        'Management Level'),
    ('Job_Role__c',         'Job Function') */
    
CREATE TABLE [dbo].[tmp_compare] 
(
    [id]      int identity(1,1),
    [H_ZI_ID] bigint
)

INSERT INTO [tmp_compare] 
VALUES
    ('1001122877'),('1001125385'),('1002260105'),('100233801'),('1002661679')

CREATE TABLE [dbo].[h_processed] 
(
    [Contact ID] varchar(255),
    [Job Title]  varchar(255)
)

INSERT INTO [h_processed] 
VALUES ('1001122877', 'Chief Financial Officer')

CREATE TABLE [dbo].[sf_contact] 
(
    [Contact ID] varchar(255),
    [Title]  varchar(255)
)

INSERT INTO [sf_contact] 
VALUES ('1001122877', 'CFO')

CREATE TABLE [data_log] (
    [id]        int identity(1,1),
    [dataID]    varchar(500),
    [field]     varchar(128),
    [sf_data]   varchar(500),
    [h_data]    varchar(500),
    [score]     float,
    [action]    varchar(128)
);

8 rows affected

id	dataID	field	sf_data	h_data	score	action
1	1001122877	Title	CFO	Chief Financial Officer	null	Needs Review


INSERT data_log (dataID, field, sf_data, h_data, action)
SELECT
  sf.[Contact ID],
  v.column_name,
  v.sf_data,
  v.h_data,
  CASE WHEN v.sf_data = v.h_data OR v.h_data IS NULL THEN 'None'
       WHEN v.sf_data IS NULL AND v.h_data IS NOT NULL THEN 'UPDATE'
       ELSE 'Needs Review'
  END
FROM tmp_compare tc
JOIN sf_contact sf ON sf.[Contact ID] = CAST(tc.[H_ZI_ID] AS varchar(255))
JOIN h_processed hp ON hp.[Contact ID] = sf.[Contact ID]
CROSS APPLY (VALUES
      ('Title', sf.[Title], hp.[Job Title])
) v(column_name, sf_data, h_data);