Duplicates not getting ignored in SQL Server

Question

I have a temp table that has two rows. Their Id is 999359143, 999365081

I have a table that doesn't have a primary key but has a unique index based off of the id and date.

This 999359143 already exists in the table. So when I use my query it still is trying to insert the row from the temp table into the normal table and it errors. This is the query below

INSERT INTO [XferTable]
           ([DataDate]
           ,[LoanNum]
          )

SELECT Distinct t1.[DataDate]
           ,t1.[LoanNum]               
FROM #AllXfers t1 WITH(HOLDLOCK) 
WHERE NOT EXISTS(SELECT t2.LoanNum, t2.DataDate
                    FROM XferTable t2 WITH(HOLDLOCK) 
                   WHERE t2.LoanNum = t1.LoanNum AND t2.DataDate = t1.DataDate
)

Is there a better way to do this?

Answer 1

You should use the MERGE statement, which acts atomically so you shouldn't need to do your own locking (also, isolation query hints on temporary tables doesn't achieve anything).

MERGE XferTable AS SOURCE
USING #AllXfers AS TARGET
ON
    SOURCE.[DataDate] = TARGET.[DataDate]
    AND SOURCE.[LoanNum] = TARGET.[LoanNum]
WHEN NOT MATCHED BY TARGET--record in SOURCE but not in TARGET
THEN INSERT
(
     [DataDate]
    ,[LoanNum]
)
VALUES
(
     SOURCE.[DataDate]
    ,TARGET.[LoanNum]
);

Your primary key violation is probably because you are using (Date, Loan#) as the uniqueness criteria and your primary key is probably only on Loan#.