Somewhat confusing, but I think this may return the rows you want:

SELECT T.*, Z.*
FROM
#TEMP1 T
INNER JOIN
[TSI].[ZATS_BROKER_FEED] Z
ON T.Part_Num...

Non-clustered index(es) are almost always a waste of resources on a staging table.  

A clustered index is much more likely to be useful, although it's obviously not guaranteed to...

You don't really need a cursor for what you're described.

DECLARE @job_name_pattern nvarchar(128)
DECLARE @sql nvarchar(max)

SET @job_name_pattern = 'MyJobEvery%'

SELECT @sql = CAST((

set statistics io,time off

DROP TABLE latestrecords ;
GO
CREATE TABLE latestrecords
(
Id int,
Email varchar(200),
Customer_FirstName varchar(200),
Status varchar(200),
RecordTimestamp DATETIME
)
GO

The imperfect index definition is a drag on the first query, requiring a sort of all rows.  Correcting the index definition:

DROP INDEX IX_latestrecords_1 ON latestrecords;
CREATE UNIQUE INDEX...

Not enough details to get specific, but in very general terms, I would likely go with a new option.

Keep the existing tables to do the current logging, but...

Consider using system views such as sys.dm_db_index_operational_stats. Presumably you know at least the db(s) in which the code operates, although hopefully object names as well (or at least most...