Solved

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

Posted on 2014-11-17
5
124 Views
Last Modified: 2014-11-17
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
0
Comment
Question by:ScuzzyJo
  • 2
  • 2
5 Comments
 
LVL 32

Expert Comment

by:ste5an
ID: 40447121
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
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 40447154
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
 

Author Comment

by:ScuzzyJo
ID: 40447268
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
 
LVL 32

Accepted Solution

by:
ste5an earned 500 total points
ID: 40447311
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
 

Author Comment

by:ScuzzyJo
ID: 40447375
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

Featured Post

IT, Stop Being Called Into Every Meeting

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

758 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

23 Experts available now in Live!

Get 1:1 Help Now