Daily:
The first thing I check each morning when I start work is if any of my backups failed.  
Next I check for any failed SQL jobs on any...

This sounds like a job for a trigger.

You would create an AFTER INSERT trigger on tbl1 and in that you put your append query.
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql
Syntax:

I just tried a subset of this on my system (SQL 2016 SP1) and got the same result you did.  My subset test was:
CREATE TABLE #test (s...

When you run these scripts, do you run the entire SB_Target_master.txt on the target server and then run the entire SB_Source_master.txt or even in the reverse order?  

The...

I could be wrong on this as I have not built a trigger like that before, but I fairly confident that the INSERTED table is only visible to your trigger,...

I may be mistaken on this, but I think that the problem is that @Dimension is not containing a single int value.  You say that it is multiple selects.  So...

I agree with Sue here; the new CE wasn't indroduced in 2012.  Plus the OP indicated that updating statistics on the 2008 one caused the 10 minute run as well.

I am glad you figured it out.  I am a little confused why what I wrote didn't work though.

Did you change any of the code I posted?  It...

From my understanding, UNMASK is a database level permission.
I think what would likely be the best way to manage this would be to create a user without login that...

for others wanting to help, here is the DDL
DECLARE @tblLineItems TABLE
    (
        [LineItemID] INT ,
        [JobID] INT
    );
DECLARE @tblCostCodes TABLE
    (
        [costCodeID] INT ,
        [costCode] VARCHAR(25)

rn is the "rn" column from the cte which is the row number partitioned by staff_id, ordered by transaction_Date
What version of SQL are you using?  I ran the above...