?
Solved

SQL - the use of EXCEPT

Posted on 2012-09-14
4
Medium Priority
?
256 Views
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?
0
Comment
Question by:tommym121
  • 2
  • 2
4 Comments
 
LVL 32

Assisted Solution

by:Ephraim Wangoya
Ephraim Wangoya earned 2000 total points
ID: 38400110
Have you looked at the possibility that your table may have duplicate records
0
 

Author Comment

by:tommym121
ID: 38400332
How do I find out if there are duplicate records if there is no keys for these table at all
0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 2000 total points
ID: 38401223
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
as
(
	select *, ROW_NUMBER() over (partition by num, alpha order by num) RN
	from @dups
)

select *
from cte
where RN > 1

Open in new window

0
 

Author Closing Comment

by:tommym121
ID: 38404184
Thanks
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 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