How to log value that cause unique key violation in SQL Server

Question

I am inserting data into a table from temp table as below:

INSERT INTO TBL_PERSON (PropertyID, PERSONID, FULLNAME)          
SELECT DISTINCT PropertyID, PERSONID, FULLNAME FROM #SalesContacts 

The unique key constraint is defined in Tbl_Person using combination of PropertyId, PersonId and FullName columns.

When this query is executed, how can I find out which combination of values are causing unique key constraint exceptions?

I want to log these values with the exception message.

Answer 1

is there a reason this logic won't work for you?

--Find all duplicates and save to temp table
select * into #Duplicates from
(
    SELECT DISTINCT PropertyID, PERSONID, FULLNAME FROM #SalesContacts 
    INTERSECT
    SELECT  PropertyID, PERSONID, FULLNAME FROM TBL_PERSON
) a

--Log the duplicates
insert into ErrorLoggingTable(PropertyID, PERSONID, FULLNAME, ErrorMessage)
select PropertyID, PERSONID, FULLNAME, 'Duplicate Found' from #Duplicates

--Remove the duplicates from the #SalesContacts table
delete a from #SalesContacts a join
#Duplicates b on b.PropertyID and b.PersonID and b.FullName

--Insert the rest
insert into TBL_PERSON (PropertyID, PERSONID, FULLNAME)
    select PropertyID, PERSONID, FULLNAME