I have two tables
t_DTM_DATA_STAGING around 2 million records
t_DTM_DATA around 251 million records
The below SQL statement looks for records in the t_DTM_DATA_STAGING table that are not in the t_DTM_DATA table and adds them to a 3rd table. (t_DTM_DATA_STAGING2)
This statement has been running fine for weeks, but now it seems to get hung every day. I ran sp_Who2 and it says the status is runnable. I let it run for around 5 or 6 hours the other day to see if it will finish but it didn't. This SQL job is step 3 in a 6 step SQLAgent job that usually finishes in 30 to 45 minutes.
I'm not sure how to troubleshoot this problem. No other jobs are running at the time this job runs.
Could this SQL statement be written a better way?
Thanks for any help anyone can provide.
SET QUOTED_IDENTIFIER ON
t_DTM_DATA_STAGING.CP, t_DTM_DATA_STAGING.CO, t_DTM_DATA_STAGING.MAJ, t_DTM_DATA_STAGING.MINR, t_DTM_DATA_STAGING.LOCN, t_DTM_DATA_STAGING.DPT,
t_DTM_DATA_STAGING.YEAR, t_DTM_DATA_STAGING.PD, t_DTM_DATA_STAGING.WK, t_DTM_DATA_STAGING.TRDT, t_DTM_DATA_STAGING.SYSTEM, t_DTM_DATA_STAGING.AMOUNT,
t_DTM_DATA_STAGING.DESCRIPTION, t_DTM_DATA_STAGING.[GROUP], t_DTM_DATA_STAGING.VENDOR, t_DTM_DATA_STAGING.INVOICE, t_DTM_DATA_STAGING.IDAT,
t_DTM_DATA_STAGING.PO_NUMBER, t_DTM_DATA_STAGING.DDAT, t_DTM_DATA_STAGING.RCV#, t_DTM_DATA_STAGING.RDAT, t_DTM_DATA_STAGING.RSP,
t_DTM_DATA_STAGING.EXPLANATION, t_DTM_DATA_STAGING.UPLOAD_DATE, t_DTM_DATA_STAGING.UPLOAD_USER, t_DTM_DATA_STAGING.UPLOAD_NAME,
t_DTM_DATA_STAGING.RELEASE_DATE, t_DTM_DATA_STAGING.RELEASE_USER, t_DTM_DATA_STAGING.RELEASE_NAME, t_DTM_DATA_STAGING.TRTM
LEFT OUTER JOIN
t_DTM_DATA AS t_DTM_DATA_1
t_DTM_DATA_STAGING.TRTM = t_DTM_DATA_1.TRTM
t_DTM_DATA_STAGING.TRDT = t_DTM_DATA_1.TRDT
t_DTM_DATA_STAGING.PD = t_DTM_DATA_1.PD
t_DTM_DATA_STAGING.YEAR = t_DTM_DATA_1.YEAR
t_DTM_DATA_STAGING.DPT = t_DTM_DATA_1.DPT
t_DTM_DATA_STAGING.LOCN = t_DTM_DATA_1.LOCN
t_DTM_DATA_STAGING.MINR = t_DTM_DATA_1.MINR
t_DTM_DATA_STAGING.MAJ = t_DTM_DATA_1.MAJ
t_DTM_DATA_STAGING.CO = t_DTM_DATA_1.CO
t_DTM_DATA_STAGING.CP = t_DTM_DATA_1.CP
(t_DTM_DATA_1.CP IS NULL)