February 26, 2013 at 10:35 am
Hello everyone
I am working on a problem where I need to compare the data in two tables. I can have some columns in the table that will be the same, those I use to join on. But a couple columns in the table, will be changing. If the values have changed, I need to update the data in the other table.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#OriginalData','U') IS NOT NULL
DROP TABLE #OriginalData
--===== Create the test table with
CREATE TABLE #OriginalData
(
RowID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
, MemberID int
, LocationID varchar(5)
, FirstName varchar(25)
, LastName varchar(25)
, Birthdate date
, AreaCode int
, PhoneNumber varchar(8)
)
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#UpdatedData','U') IS NOT NULL
DROP TABLE #UpdatedData
--===== Create the test table with
CREATE TABLE #UpdatedData
(
RowID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
, MemberID int
, LocationID varchar(5)
, FirstName varchar(25)
, LastName varchar(25)
, Birthdate date
, AreaCode int
, PhoneNumber varchar(8)
)
INSERT INTO #OriginalData
(MemberID, LocationID, FirstName, LastName, Birthdate, AreaCode, PhoneNumber)
SELECT 222, 'Viol','Sammy','Miller','1965-12-15', 888, '555-1212' UNION ALL
SELECT 223, 'Green','Sarah','Smith','1968-03-02', 523,'458-2356' UNION ALL
SELECT 224, 'Green','Michelle','Foster','1966-12-12', 657,'241-7815' UNION ALL
SELECT 225, 'Gray', 'Robert','Gillum','1970-07-20', 879,'251-6300' UNION ALL
SELECT 226, 'Red', 'Joe','Roberts','1960-05-05', 211,'205-8785' UNION ALL
SELECT 227, 'Red', 'Danny','Jones','1961-10-30', 696,'575-5611' UNION ALL
SELECT 228, 'Blue', 'Andy','Hill','1966-08-01', 315,'473-2501' UNION ALL
SELECT 229, 'Blue', 'Bill','Height','1962-11-20', 315,'474-5670'
INSERT INTO #UpdatedData
(MemberID, LocationID, FirstName, LastName, Birthdate, AreaCode, PhoneNumber)
SELECT 222, 'Viol', 'Sammy','Miller','1965-12-15', 888, '555-1212' UNION ALL
SELECT 223, 'Green', 'Sarah','Smith','1968-03-02', 523,'458-2356' UNION ALL
SELECT 224, 'Green', 'Michelle','Foster','1966-12-12', 657,'241-7800' UNION ALL -- Changed PhoneNumber
SELECT 225, 'Gray', 'Robert','Gillum','1970-07-20', 879,'251-6300' UNION ALL
SELECT 226, 'Red', 'Joe','Roberts','1960-05-07', 211,'205-8585' UNION ALL -- Changed Birthdate, PhoneNumber
SELECT 227, 'Red', 'Danny','Jones','1961-10-30', 696,'575-5611' UNION ALL
SELECT 228, 'Blue', 'Andrew','Hill','1966-08-01', 315,'473-2502' UNION ALL -- Changed FirstName, PhoneNumber
SELECT 229, 'Blue', 'Bill','Height','1962-11-20', 315,'474-5670'
SELECT * FROM #OriginalData
SELECT * FROM #UpdatedData
DROP TABLE #OriginalData
DROP TABLE #UpdatedData
MemberID and LocationID are the only two columns that can never change.
If any row in the #UpdatedData table, except for MemberID or LocationID is different that Update #OriginalData row with the same row in the #UpdatedData table. As you can see, I have set 3 rows in the #UpdatedData table to be different than the rows in the #OriginalData table.
Ignore all rows that have not changed, or are different between the two tables.
So, my issues is, how can I identify the rows that are different between the two tables?
Thank You in advance for all your assistance, advise and suggestions. This one is driving me crazy, I am coming up with a huge number of rows in my query, where in fact, only a couple rows have actually changed. I have tried a couple different JOINs to join the table tables, but something is not quite correct.
Andrew SQLDBA
February 26, 2013 at 11:27 am
A MERGE statement might get you where you need to be or at least closer to a solution.
http://technet.microsoft.com/en-us/library/bb510625(v=sql.105).aspx
MERGE #UpdatedData AS target_tbl
USING #OriginalData AS source_tbl
ON target_tbl.MemberID = source_tbl.MemberID
WHEN MATCHED AND target_tbl.FirstName != source_tbl.FirstName THEN
UPDATE SET target_tbl.FirstName = source_tbl.FirstName
OUTPUT $action, Inserted.MemberID;
_____________________________________________________________________
- Nate
February 26, 2013 at 11:43 am
I am working with that now. But if I just query the two tables, I am getting a huge number of rows back, when I know there are only two rows that are different between the two tables. I know, because I changed those two rows.
I appreciate your suggestion. I am still trying to figure this one out. But I will use MERGE once I get this other figured out
Thank You Greatly
Andrew SQLDBA
February 26, 2013 at 11:49 am
Did you look at EXCEPT operator...may be of use to you.
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/b1019300-171a-4a1a-854f-e1e751de3565.htm
February 26, 2013 at 11:44 pm
There are 3 rows which are different as per the sample data provided
UPDATEOD
SETOD.FirstName = UD.FirstName,
OD.LastName = UD.LastName,
OD.Birthdate = UD.Birthdate,
OD.AreaCode = UD.AreaCode,
OD.PhoneNumber = UD.PhoneNumber
FROM#OriginalData AS OD
INNER JOIN #UpdatedData AS UD
ONOD.MemberID = UD.MemberID AND OD.LocationID = UD.LocationID
WHEREOD.FirstName != UD.FirstName
OROD.LastName != UD.LastName
OROD.Birthdate != UD.Birthdate
OROD.AreaCode != UD.AreaCode
OROD.PhoneNumber != UD.PhoneNumber
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 27, 2013 at 7:46 am
Kingston Dhasian (2/26/2013)
There are 3 rows which are different as per the sample data provided
UPDATEODSETOD.FirstName = UD.FirstName,
OD.LastName = UD.LastName,
OD.Birthdate = UD.Birthdate,
OD.AreaCode = UD.AreaCode,
OD.PhoneNumber = UD.PhoneNumber
FROM#OriginalData AS OD
INNER JOIN #UpdatedData AS UD
ONOD.MemberID = UD.MemberID AND OD.LocationID = UD.LocationID
WHEREOD.FirstName != UD.FirstName
OROD.LastName != UD.LastName
OROD.Birthdate != UD.Birthdate
OROD.AreaCode != UD.AreaCode
OROD.PhoneNumber != UD.PhoneNumber
Oh, be careful. You could have quite the surprise if NULLs are involved. I'm a bit stuck in the world of 2005 so haven't had to work with MERGE but I believe the same problem with NULLs may exist there, as well.
You might want to try EXCEPT which does observe NULLs.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 27, 2013 at 8:20 am
Anything stopping you just doing this?
MERGE #OriginalData original
USING #UpdatedData changed ON original.MemberID = changed.MemberID AND original.LocationID = changed.LocationID
WHEN MATCHED AND EXISTS (SELECT original.MemberID, original.LocationID, original.FirstName, original.LastName, original.Birthdate, original.AreaCode, original.PhoneNumber
EXCEPT
SELECT changed.MemberID, changed.LocationID, changed.FirstName, changed.LastName, changed.Birthdate, changed.AreaCode, changed.PhoneNumber)
THEN UPDATE SET original.FirstName = changed.FirstName,
original.LastName = changed.LastName,
original.Birthdate = changed.Birthdate,
original.AreaCode = changed.AreaCode,
original.PhoneNumber = changed.PhoneNumber;
I might be over-simplifying, if so, sorry :w00t:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply