SQL - the use of EXCEPT

Posted on 2012-09-14
Last Modified: 2012-09-16
I have a table 'Mytable' over 170k of records,  I have another table I called 'MytableLastUpdate'. I insert data to the 3rd table called MytableToBeUpdated by find the difference between Mytable and MytableLastUpdate. This is the query I used

Select * insert into MytableToBeUpdated  from Mytable Except Select * From MytableLastUpdate

All of them have the same schema. MytableLastUpdate is empty prior of running the query.

The result of the query indicate only 160 records is being inserted.

When I compare the two tables - Mytable and MytableToBeUpdated  with these two queries below
Select *   from Mytable Except Select * From MytableToBeUpdated
 Select *   from MytableToBeUpdated Except Select * From Mytable

The output is empty meaning no records are different and no extra records. What is going on here? Does it make sense to anyone at all?
Question by:tommym121
    LVL 32

    Assisted Solution

    Have you looked at the possibility that your table may have duplicate records

    Author Comment

    How do I find out if there are duplicate records if there is no keys for these table at all
    LVL 32

    Accepted Solution

    You dont need a key field to find if you have duplicates, you can use a CTE, ROW_NUMBER function and PARTITION to find the duplicates, here is an example

    declare @dups table(num int, alpha char(1))
    insert into @dups
    select 1, 'A'
    union all select 1, 'A'
    union all select 1, 'A'
    union all select 1, 'B'
    union all select 2, 'D'
    union all select 2, 'E'
    union all select 2, 'D'
    ;with cte
    	select *, ROW_NUMBER() over (partition by num, alpha order by num) RN
    	from @dups
    select *
    from cte
    where RN > 1

    Open in new window


    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
    Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    761 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

    8 Experts available now in Live!

    Get 1:1 Help Now