Update/Insert Records In Table Based Off Of Match In Another Table

Posted on 2011-02-21
Last Modified: 2012-06-22
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.
Question by:MattStewart01
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 17

Expert Comment

ID: 34946483
I think this would be pretty easily done.  Update if exists, otherwise insert. Can you provide example data from both the person and demographicdata tables?

Author Comment

ID: 34952552
Person Table
PersonID      FirstName      LastName      DOB                  Address
1                  Matt            Smith            1/13/1980      123 Main St.
2                  Eric            Brown            2/11/1970      355 Bay St.
3                John            Stewart            7/1/1984      1453 City Ln.
4                  Sam             Smith            6/1/1982      463 Oak St.

DemographicData Table
FileDate      FirstName      LastName      DOB                  Address
1/5/2011      Matt            Smith            1/13/1980      123 Green St.
1/5/2011      Emily            Thomas            6/27/1974      1654 Old West St.
1/5/2011      Thomas            Green            11/4/1990      633 King St.
9/1/2010      Eric            Brown            2/11/1970      355 Bay St.
9/1/2010    John            Stewart            7/1/1984      1453 City Ln.
9/1/2010      Sam             Smith            6/1/1982      463 Oak St.
9/1/2010      Matt            Smith            1/13/1980      123 Main St.

So in this example that I just made up there a list of persons in the Person table. Each one of these persons could have more than one record in the DemographicData table. So I want to loop through each row in the DemographicData table and look for a match in the Person table. So the query should see that Matt Smith has a match in the Person table, but it also sees that there is more than one record for that person in the DemographicData table. So it will look for the most recent FileDate for Matt Smith and update his Address since it has changed. The query would also add any new persons that it finds, so in this case it would add Emily Thomas and Thomas Green. At a future date I will be adding a processed flag so that DemographicData records will only be processed once, but at this point we are starting from scratch going back through serveal years worth of person files so I have to go through the entire DemographicData table.

Again this is overly simplified, but it should give you a good idea of what I am trying to do. I can do it with a cursor, but it would litterally take days to run.
LVL 17

Accepted Solution

dbaSQL earned 500 total points
ID: 34957601
My apologies for the delay, MattStewart01.  Bit of a family emergency.  Just wanted to add this in real quickly.  You can do this as-is, by passing in your @firstname and @lastname, or you could cursor through the FirstName and LastName values within the DemographicData table.  

I just did it with Matt Smith, and the Address was updated correctly.  Try it with Emily Thomas, and see if you get that insert into the Person table.
DECLARE @filedate date,
		@firstname varchar(25),
		@lastname varchar(25)
SET @firstname = 'Matt'
SET @lastname = 'Smith'		
SELECT 1 FROM dbo.Person p JOIN dbo.DemographicData d
  ON p.FirstName = d.FirstName
  AND p.LastName = d.LastName
  WHERE p.FirstName = @firstname
  AND p.LastName  = @lastname
	SELECT @filedate = MAX(FileDate) FROM dbo.DemographicData d WHERE d.FirstName = @firstname AND d.LastName = @lastname
	SET [Address] = p.Address
	FROM dbo.Person p JOIN dbo.DemographicData d
      ON p.FirstName = d.FirstName
      AND p.LastName = d.LastName
	WHERE d.Address <> p.Address
	AND d.FileDate = @filedate	
	SELECT 1 FROM dbo.Person p
	WHERE p.FirstName = @firstname
	AND p.LastName = @lastname
		INSERT dbo.Person (FirstName, LastName, DOB, [Address])
		SELECT FirstName,LastName,DOB,[Address]
		FROM dbo.DemographicData 

Open in new window


Author Comment

ID: 34957619
This actually works perfectly. Thanks for all of the help!
LVL 17

Expert Comment

ID: 34957631
Excellent.  Glad to have helped.

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question