Improve company productivity with a Business Account.Sign Up


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

Posted on 2011-02-21
Medium Priority
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
  • 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 2000 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

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

606 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