You have to filter out "duplicate values" in your data set before you try to insert them into the destination table.

You can do this by assigning a row number using the ROW_NUMBER() function. You can assign row numbers inside a partition, which is in this case the Email column. Then you keep only rows where the row number is 1.

SELECT Name, Email

FROM

(

SELECT Name, Email, rid = ROW_NUMBER() OVER (PARTITION BY Email ORDER BY Name)

FROM #TestTable

) tmp

WHERE rid = 1;