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...

SQL doesn't by default have an "order" to return your result set by.  So when you issue a command like "SELECT Transaction_Date FROM Employee_Change", depending on various factors, you could...

You are missing some data there... or I misunderstand the request.
is the attached table the expected output, the sample input or something else?  If it is the sample input,...

We actually have a similar setup where I work and it was a lot of trial and error to get thigns to work optimally.
Even after we thought we had...

I've been trying to reply to this thread all day.

I apologize for the misinformation.  I did more snooping and I am definitely incorrect in what I had posted....

Possibly?  I personally have not experienced that specific issue.

Are you using BIDS 2008 (inside Visual Studio 2008) to create the report?  I've seen other SQL things have issues...