Update/Insert Records In Table Based Off Of Match In Another Table
Posted on 2011-02-21
I have two tables that both contain customer information. Lets call one table "Person" and the other table "DemographicData". The Person table contains person's names, and contact information. The DemographicData table has historical data about a person including their names and contact info. When a person updates their contact information, a new record is added to the DemographicData table with the date that it was changed. So one person may have multiple records in the DemographicData table. This allows us to track the person's history.
What I want to do is update the Person table with the most up-to-date contact information for that person from the DemographicData table by matching on FirstName, LastName, and DOB. If a match is not found in the DemographicData table then I want to add a new record to Person with the most up to date contact info for that person from DemographicData. So I beleive that I want to perform some type of "Upsert" like query, but I do not know how to do it with out using a cursor to go through each record in DemograhicData one at a time.
I have really simplified the enviroment that I am working with, but hopefully this is enough information. Thanks for any help.