>> I am considering dropping the database backups processes on the argument that we can always restore the VM and replay the transaction logs.
Are you in a position to...

You have not really provided enough detail.

Assuming the column to be updated is in the same row, try the following outline:

UPDATE Y
SET YourUpdateColumn = 'WhatYouWantToUpdate'

If you want an EXISTS equivalent to the IN try:

SELECT adminproduct_id,rowversionnumber,therapyadmin_id,packagedproduct_id,adminrecordmethod
   ,actioncode,doseamount,doseunits,dispensableproduct_id,dispensesize,routedproduct_id
   ,dosetext,lotnumber, expirationdate,manufacturercode
FROM hcs.adminproduct P
WHERE EXISTS
(
    SELECT 1
    FROM hcs.therapyadmin T
    WHERE T.therapyadmin_id...

If you are on at least SQL2012, I suspect the easiest approach would be to create a sequence and then add that sequence as the default constraint on the column.

You probably need to ensure that all the filters on the outer table are in the JOIN and not the WHERE clause.
Logically the FROM clause, with JOINS, is processed...

Sounds like a race condition on insert.
I would forget about the explicit transaction and run the following:

UPDATE t1
SET table1.column1 = l.staging_2,
table1.column2 = l.staging_3

Also, check the number of VLFs on the source db.

DBCC LOGINFO


If the number of rows is greater than about 200 look at reducing them:

Your problem is the cursor.
It is difficult to write a set based approach without DDL and comsumable test data.
Try something like the following:

--declare @TransactionEffDate DATETIME2(3) =...

Bulk-logged still has a couple of problems:
1. Point in time recovery will not work for any time the DB was in bulk-logged mode.
2. While bulk-logged recovery logs less...

You might be able to use fn_dump_dblog to read your log backups:

https://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/