First, you should encode the string values to a numeric equivalent.  Imagine what must happen now when the name of a level changes.  For example, say that "Outdoor Accessories" had...

For just that specific column, you need to rebuild one of the existing indexes:

CREATE NONCLUSTERED INDEX [IX_game_boxscore_players_personID] 
    ON [mlb_json].[game_boxscore_players] ( [boxscore.players.person.id] ) 
...

Need to see the DDL including the index definitions for all tables in the query.

I still have to wonder on the appropriate use of NOLOCK. Even if the data does not change in a table, is NOLOCK...

I still have to wonder on the appropriate use of NOLOCK. Even if the data does not change in a table, is NOLOCK that much better...

For these specific queries alone -- since we know nothing else about this table -- you should change the clustering key on the table shown.

CREATE TABLE dbo.CustTravelReqLocPlan (

...

CONSTRAINT [PK__CustTrav__401DC63546C0E5F6] PRIMARY...

It seems there must be some error occurring when the script it exec'd.  Add some msgs to (1) verify if this is true and (2) if so, show the return...

(1) CROSS JOIN is not necessarily bad for performance.  If the CJ'd table is single row, it's definitely fine.

(2) The %s are OK here, since there's no easier way to...

Yes, both can make a big difference in OLTP as well.  The key is that it reduces I/O because fewer pages must be written to disk.

Vastly better to instead create a clustered index before you load the temp table.

I still have to wonder on the appropriate use of NOLOCK. Even if the data does not change in a table, is NOLOCK that much better than a standard...

Very similar, but using an OUTER APPLY because you said you used a LEFT JOIN.  OUTER APPLY is like a "LEFT APPLY" and CROSS APPLY is like an "INNER APPLY".

Don't...

Sorry, I just have to ask: why would logical file name(s_ need to be preserved?  Are they hard-coded in some existing statements?  That seems dangerous, what if files are added...

I would have thought you could just rename the logical names to end up with the same name.

Either: rename the original logical file name before you add the new file...

Hmm, maybe add another file to the filegroup in the new location.

Then remove the existing file from the filegroup; since the file's empty, you should be able to remove it.