The OFAC data is imported from the XML file the USTD provides to its own database (multiple tables). The import program drops/recreates the tables each time.

The main identity table (sdnentry) has a unique ID that stays the same from version to version from the USTD.

I have a separate table to maintain the one to many relationship between our clients and the potential matches. So each time the OFAC data is updated and we compare against our current client list, we know which ones were potential matches before and only need to check/verify any new matches. I actually run the check once each day as one of our daily processes. This compares any new clients that were added during the day.

As for what I use to compare - the data in the OFAC list is 'ugly' to be polite, so doing a straight string compare would miss anything unless they matched exactly. So, I use a simply SOUNDEX methodology. Nothing special - but a heck of a lot better than what the USTD/SEC websites provide.

To do the SOUNDEX comparison, I use the TSQL DIFFERENCE function with a value of 4. I have found that gets any matches that are close and accounts for the most common english misspellings in names. Its not sophisticated, but it works.

I did find a commercial library (no longer have the name) that worked exceptionally well- but they wanted a huge sum to license (over 25k min) and it was very restrictive in its use.