Finding missing records in SQL table
The table stored in mySQL database contains the ID column, which is a number between 2000000 and 4000000. The numbers are ordered and sequential (increment by 1). I need a SQL query which can loop through this range and find all missing records (the database should contain 2000000 records, but currently contains only 1800000).
Thank you, experts!
Thank you, experts!
ASKER
I would need a script that populates the new table you are suggesting.
Also, there should be a simpler way finding the missing records by using a cursor and a single query.
Also, there should be a simpler way finding the missing records by using a cursor and a single query.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Tomas, I tried running the query but it never came back... Can you please explain the logic behind it?
... there should be a simpler way finding the missing recordsReally? How often do you need to do this? If it's more than once, there is something suspicious about the overall design. My sense is that any solution would be the best solution. And with only two million records it's not a large process.
Please post the CREATE TABLE statement and show us some of your test data. Armed with that we can almost certainly give you a tested-and-working code sample.
create table numbers (N bigint);
then do a loop that inserts the numbers you need.
Next select the numbers that have no counterpart in your table.
Select N from numbers where not exists (Select ID from MyTable where ID = N);