Solved

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

Posted on 2011-02-21
5
489 Views
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.
0
Comment
Question by:MattStewart01
  • 3
  • 2
5 Comments
 
LVL 17

Expert Comment

by:dbaSQL
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?
0
 

Author Comment

by:MattStewart01
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.
0
 
LVL 17

Accepted Solution

by:
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'		
 
IF EXISTS(
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
  )	
  BEGIN
	SELECT @filedate = MAX(FileDate) FROM dbo.DemographicData d WHERE d.FirstName = @firstname AND d.LastName = @lastname
	
	UPDATE d
	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	
  END
  ELSE
  BEGIN
	IF NOT EXISTS(
	SELECT 1 FROM dbo.Person p
	WHERE p.FirstName = @firstname
	AND p.LastName = @lastname
	)
	BEGIN
		INSERT dbo.Person (FirstName, LastName, DOB, [Address])
		SELECT FirstName,LastName,DOB,[Address]
		FROM dbo.DemographicData 
    END
  END

Open in new window

0
 

Author Comment

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

Expert Comment

by:dbaSQL
ID: 34957631
Excellent.  Glad to have helped.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now