I have to admit that when I read the following, I thought that Seth had simply lost his mind...
My quick resolution in this situation is to:
1. Remove the unique index temporarily;
2. Run the application, allowing it to insert duplicate item(s);
3. then find the duplicate(s) and remove them.
Of course, these steps are preceded by performing a good backup of the database and possibly putting the database in single user mode to prevent unexpected query results during my work. As simple as the task of removing a record with a duplicate value sounds, it can get confusing, and I need to proceed with care. To be safe, I follow this rule of thumb: first I perform a SELECT of the record(s) that will be removed, then I convert it to a DELETE statement after I'm sure it will affect only record(s) that I want it to.
.. because it just wasn't clear that it was a legacy app that shouldn't be changed because of the impending rewrite. I thought that was an awful lot of work to do a simple conditional insert.
Now that Seth has clarified the problem a bit, I can mostly agree with the pain he goes through including that of duplicate elimination. On that subject and for all of those that made the very good suggestion of using ROW_NUMBER() to isolate duplicates, keep in mind that this is a legacy app on a legacy DB and it might be pre-2k5 where ROW_NUMBER() simply doesn't exist. Still, the title of the article is "Get Rid of Duplicates" and not "Get Rid of Duplicates for a Special Case" and I can certainly understand why people may have jumped to the wrong conclusion on this article especially when the wrap-up line in the Conclusion is "Now you can confidently remove duplicate records from your tables!" and there was no mention of version nor ROW_NUMBER(). 😉
That notwithstanding, for what the article was actually about, it was a good, well written article. Thanks, Seth.
As a side bar... I don't know what the app would do with the [font="Courier New"]"Duplicate key was ignored."[/font] message that would show up you tried to insert any dupes (some apps interpret such messages as an error... same goes with returned row counts), but have you tried changing the unique index to a unique index with the "IGNORE_DUP_KEY = ON" setting? If the app forgives the warning message(s) about dupes being ignored (1 for each INSERT statment that has dupes no matter how many dupes exist in that INSERT), it could save you a wad of trouble that you're currently going through.
--Jeff Moden
Change is inevitable... Change for the better is not.