Why not do it all in one go, something like this?  The code may not be 100% correct since you didn't provide any consumable DDL or sample data to test...

Do you have Instant File Initialization enabled on the new server?  If not, you may find that a large proportion of the time is spent zeroing out the data files...

Have you considered using Full-Text Search?

John

You don't want to do the calculation twice if you don't have to.  Try this:

SELECT CONCAT(F.FloorWeight,'lbs ',(W.WeightinPounds - F.FloorWeight)*16,'oz') as PtWeight
FROM #T t
CROSS APPLY...

No, just subtract the integer part, which you get by using FLOOR.

John

Multiply by 2.2 to convert to pounds and multiply the resulting decimal part by 16 to get the ounces.

John

Those aren't the full definitions - you haven't included primary keys or any other constraints.  Since the error is a primary key violation, it's important to know something about the...

How often does this happen?  The best place to start, I think, would be to download sp_whoisactive (if you don't already have it) and run it next time tempdb is...

Without table DDL and sample data, we're just guessing.  However, this may be close to what you're looking for:

WITH NumberedbyMachine AS (
    SELECT
        RV.Name0,
        UCS.Status,
        UCS.LastStatusChangeTime,

I'm guessing the two correlated subqueries are slowing you down.  Each one is executed once for each row in your result set.  Please post your execution plan for help with...