how to find missing rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to find missing rows

Author

Topic

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-07-21 : 08:56:17

I have one query
select * from employee
where employee id in (1,2,3.......................999999)

but this query will give me only employee id existing from the IN clause

suppose there may be possibility that for employee id 3 does not existing ,but above query will still return me the record
of other employee id except 3

but i want to find out missing records e.g assume employee id 3 missing.provided I have set of standard employee ID records in a CSV file,so is there a way to compare the the records from reference CSV file's key value with perticular table's key value?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-07-21 : 09:47:39

you need a "tally" table containing numbers 1-9999999999 (or whenever you want to stop)

select * from tally a
left join employee b on b.code = a.number
where b.code is null

Devart
Posting Yak Master

102 Posts

Posted - 2010-07-21 : 10:10:58

Hello!

If you use MSSQL 2005 or higher, try this:

with _cte(id) as
(
select 1 as id
union all
select id+1 from _cte where id<100
)

select
t.*,
e.*
from (select row_number() over(order by x.id) as [employee id] from _cte x,_cte c,_cte y) t
left join employee e
on e.[employee id] = t.[employee id]
where
e.[employee id] is null

Best regards,

Devart Team

Kristen
Test

22859 Posts

Posted - 2010-07-21 : 12:42:02

Still need to tie the tally table to the CSV list?

If so might as well split the CSV list to get the "tally" list?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources