I'm trying to write a SQL query that will compare the same data in two tables, one is the original data and another will be an updated version of this data. I want to compare the two datasets for any changes and return those changes. That I have managed to do, but I'd like to return the results in different rows not in the same row. So currently my result set looks like this:
I can see that it returns two Staff members who have changes in their details. Joe Smyth's name was wrong and has been corrected to Joe Smith and Sally Green has changed from Ms to Mrs. However, I'd like my returned dataset to be in this shape:
This is the query I used to put it this way:
First I returned any changes to a temp table.
Then pulled the data I wanted from the different data sets using UNION to join them:
SELECT b.StaffNumber INTO #StaffNumbers FROM OriginalData a LEFT JOIN NewData b ON a.StaffNumber = b.StaffNumber AND (a.Name != b.Name OR a.Title != b.Title) WHERE b.StaffNumber IS NOT NULL SELECT * FROM ( SELECT 'UPDATE' as [Type Of Change], 'Original Data' as [Data Source], StaffNumber as [Staff Number], Name, Title FROM App_Person a INNER JOIN #GmcNumber b ON a.StaffNumber = b.StaffNumber UNION SELECT 'UPDATE' as [Type Of Change], 'New Data' as [Data Source], a.StaffNumber as [Staff Number], Name, Title, FROM PersonExtract a INNER JOIN #StaffNumbers b ON a.StaffNumber = b.StaffNumber ) sub1 ORDER BY [Staff Number]
So while I'm getting the result I want, I'm wondering if there's a smarter way to do this than what I've done above. If anybody has any ideas/suggestions.