SQL Using Except to compare all columns and output differences to new table

Hi

I hope you can help as I've tried various methods, including some I found on here, but none are working as I would expect.

I have two tables, one from last week (SAS_T_All_Data_PW) and one from this week (SAS_T_All_Data) and I basically need to see what has changed in the data.  I would like to output this to a new table so that I can use it to do other stuff.  I need to compare all the columns.  So far, I have this:

INSERT INTO Data_Ch
SELECT *
FROM SAS_T_All_Data_PW WHERE personal_id IN
(
SELECT personal_id
FROM SAS_T_All_Data_PW
EXCEPT
SELECT personal_id
FROM SAS_T_All_Data
)
--
--
INSERT INTO Data_Ch
SELECT *
FROM SAS_T_All_Data_PW WHERE personal_id Not In
(
SELECT personal_id
FROM SAS_T_All_Data_PW
EXCEPT
SELECT personal_id
FROM SAS_T_All_Data
)

It gives me all the rows and I'm pretty sure that not all of them have changed.  I think that might come from the second part but, when I ran just the first bit, I didn't get any rows at all and I know some of them have changed.

I'm not sure whether using personal_id might be part of the problem as there could be several rows with the same value.  I only put the second part in as I didn't think it would pick up any that were new otherwise, but it would be great to do this in one fell swoop.

I'm awarding 500 points, but will need to understand the solution so that I can use it again effectively.

Thanks
Sarah
ScuzzyJoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
How does this relate to changed data?

When personal_id is a candidate key, then you get only the rows which are new or were deleted:

-- Deleted rows
SELECT  personal_id
FROM    SAS_T_All_Data_PW
EXCEPT
SELECT  personal_id
FROM    SAS_T_All_Data;

-- New rows
SELECT  personal_id
FROM    SAS_T_All_Data
EXCEPT
SELECT  personal_id
FROM    SAS_T_All_Data_PW;

Open in new window

0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
Hi Sarah,

Can you explain a bit clearer what your expected output is? You sT you want to compare all your columns for changes. Does this mean you want to output each column that has chanbed? Or perhaps copy a whole changed row to your output table the moment you find a value in the row has changed?
As usual, your table definitions with some workable sample data and an output example would help greatly.
0
ScuzzyJoAuthor Commented:
Hi

st5an - I got this from http://blogs.msdn.com/b/sqlserverfaq/archive/2013/11/22/how-to-compare-the-rows-of-two-tables-and-fetch-the-differential-data.aspx.  It implies that it will show changes to data within existing rows since the person who wrote it changed a value in one row and then used this method to report that changes.  Admittedly, I had trouble getting it working so mine doesn't look like his any more, but the principle behind it came from there.

Koen - I really want a table which reports every line that is either missing from the old table or which has different data in the row.  It would take ages to up upload some sample data as it's all confidential and I'd have to go  through and randomise all the data but I (now) have a unique field to use to compare and then about a million columns for it to look through.  It should report every column for every row that is either different or missing.  Hope that helps you make sense of it.

Thanks
Sarah
0
ste5anSenior DeveloperCommented:
The EXPECT clause in the linked article require the use of all colums you like to compare, thus the asterisk (*). Your sample only compares the personal_id.

WITH    UnchangedRows
          AS ( SELECT   *
               FROM     SAS_T_All_Data
               INTERSECT
               SELECT   *
               FROM     SAS_T_All_Data_PW
             )
    SELECT  IIF(UR.personal_ID IS NULL, 1, 0) AS UnchangedBit ,
            D.*
    FROM    SAS_T_All_Data D
            LEFT JOIN UnchangedRows UR ON D.personal_id = UR.personal_id;

-- or
WITH    UnchangedRows
          AS ( SELECT   *
               FROM     SAS_T_All_Data
               INTERSECT
               SELECT   *
               FROM     SAS_T_All_Data_PW
             )
    SELECT  D.*
    FROM    SAS_T_All_Data D
            LEFT JOIN UnchangedRows UR ON D.personal_id = UR.personal_id
    WHERE   UR.personal_id IS NULL;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ScuzzyJoAuthor Commented:
Thanks Ste5an.  I'll give that a try.  I'm going to close the question and award you the points as I found a different way of doing what I want to do.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning 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.