yep!  That looks like it should work and looks more efficient than my loop approach!  And easier to tune as it isn't 52 loops!

That one looks much nicer than my...

The HEADER table will always have a value for each week  There is never a case where the header table will have a NULL for a week.

In the data table,...

Since you are using a SQL Agent Job for this, in the settings for the SQL Agent Job STEP, you can tell it to output to a file.  You should...

My opinion (I do not have SQL Server on Linux installed), I see no reason why you wouldn't be able to do that.

My expectation as to why it isn't well...

To me, I would start by looking at the process that didn't get killed.  whatever process 143 was doing, it needed an exclusive lock on pec_prod.dbo.claim and an intent exclusive...

What happens if you do that insert on the other server without using the linked server?

The error "Cursor operation conflict" makes me think that there MIGHT be a trigger on...

My 2 cents - if you can't change the query, you only have 2 options to improve performance:

1 - reduce the data set (ie archive data)

2 - indexes

My reading of...

Welcome to the world of SQL Server.

So a lot of fun things you are jumping into and probably a lot of unexpected headaches.  I will try to address things as...

Seeing the entire deadlock graph would help.  it is not likely that the query is deadlocking itself, but your query and another have a conflict that requires one of them...

My approach to this is:

1 - get it set up in a test environment

2 - put the query into a transaction

3 - roll back the transaction on completion.

The above approach...

As a bit of a sidebar, it will also seriously speed up code for things like REPLACE, especially for...

Have you checked for memory pressure?

Alternately, is this happening on a regular basis?  Like, based on what you are seeing, could it be a scheduled task?  I am wondering if...

That was an interesting question today!  Learned something new!

From a cost perspective, it will probably be cheaper to install visual studio on the developer machines rather than getting enterprise/professional licenses for all of the developers so they can...

As far as I know, SSIS is going to be complicated to do this.  You are basically going to need to build up a custom script in C# (or equivalent)...