Insert into Table ignoring duplicate values
If I run into a Violation of UNIQUE KEY constraint the first thing I do is ask myself the following questions:


Does the duplicate key already exist in the target table?
Is the key duplicated in any source table?


From answering the above you will have a good idea of where the duplicate value is sourced from and a reasonable idea of the cause. Example possibilities are:


Duplicate exists in source table
Duplicate does not exist at source, but a transform of the data causes duplicates to be returned


Ultimately you need to strip this problem back to basics, as you have the duplicate value in question you need to start digging into the data and pay close attention to the source query. Luckily in your example it is a simple query and you can easily strip out joins to see if any of those introduce the duplicate, or if indeed the duplicate exists due to non-key attributes being different therefore causing your DISTINCT to not do what you expect it to do.

I guess what I am trying to say is that there isn't necessarily a quick win for this kind of problem and you need to do some good ol' fashion detective work using the clues provided by SQL.

Moot point, but I like to explicitly list the columns on an insert so that nothing breaks if the order of columns on the target table is different to that of the source query.