Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Compare the data in two tables, If Different, Update Expand / Collapse
Author
Message
Posted Tuesday, February 26, 2013 10:35 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, November 7, 2014 2:00 PM
Points: 979, Visits: 3,364
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
Post #1424179
Posted Tuesday, February 26, 2013 11:27 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, November 20, 2014 2:30 PM
Points: 628, Visits: 870
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

@nate_hughes
Post #1424196
Posted Tuesday, February 26, 2013 11:43 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, November 7, 2014 2:00 PM
Points: 979, Visits: 3,364
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
Post #1424202
Posted Tuesday, February 26, 2013 11:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 1:55 PM
Points: 1,283, Visits: 1,089
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
Post #1424205
Posted Tuesday, February 26, 2013 11:44 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:24 AM
Points: 2,682, Visits: 4,743
There are 3 rows which are different as per the sample data provided

UPDATE	OD
SET OD.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
ON OD.MemberID = UD.MemberID AND OD.LocationID = UD.LocationID
WHERE OD.FirstName != UD.FirstName
OR OD.LastName != UD.LastName
OR OD.Birthdate != UD.Birthdate
OR OD.AreaCode != UD.AreaCode
OR OD.PhoneNumber != UD.PhoneNumber




Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1424350
Posted Wednesday, February 27, 2013 7:46 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 35,593, Visits: 32,184
Kingston Dhasian (2/26/2013)
There are 3 rows which are different as per the sample data provided

UPDATE	OD
SET OD.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
ON OD.MemberID = UD.MemberID AND OD.LocationID = UD.LocationID
WHERE OD.FirstName != UD.FirstName
OR OD.LastName != UD.LastName
OR OD.Birthdate != UD.Birthdate
OR OD.AreaCode != UD.AreaCode
OR OD.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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1424542
Posted Wednesday, February 27, 2013 8:20 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:28 PM
Points: 2,386, Visits: 7,611
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



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1424562
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse