Finding gaps in column of sequential numbers
The script below should help you with this. The performance of this is relative to the number of rows within your employee table.
For the purpose of this example I will create a fake employees table. In real life you would replace this with your existing one.
create table dbo.EmployeesExample ( EmpID char(4) PRIMARY KEY, FirstName varchar(50), LastName varchar(50) ) insert dbo.EmployeesExample VALUES('0001','Laura','Jones') insert dbo.EmployeesExample VALUES('0003','Stephanie','Smith') insert dbo.EmployeesExample VALUES('0017','William','Black') insert dbo.EmployeesExample VALUES('0020','Linda','White')
Now this is the code that finds the missing values.
create table #MissingIDS ( EmpId char(4) PRIMARY KEY ) declare @maxid int, @currentid int --Find the current maximum employee id SELECT @maxid=max(cast(empid as int)) FROM dbo.EmployeesExample select @currentid=1 --Loop around sequentially populating the temporary table with every --possible employee id while @currentid<@maxid begin insert #MissingIDS select right('0000'+cast(@currentid as varchar(4)),4) select @currentid=@currentid+1 end --delete the existing employee ids from the temporary table --by joining to the Employees table delete #MissingIDS from #MissingIDS m inner join dbo.EmployeesExample e on m.empid=e.empid --And what you have left is the missing ids select * from #MissingIDS drop table #MissingIDSA slight variation on this checks to see if the empid exists before inserting into the temporary table, thereby eliminating the need for the delete.
Now this is the code that finds the missing values.
create table #MissingIDS ( EmpId char(4) PRIMARY KEY ) declare @maxid int, @currentid int --Find the current maximum employee id SELECT @maxid=max(cast(empid as int)) FROM dbo.EmployeesExample select @currentid=1 --Loop around sequentially populating the temporary table with every --possible employee id while @currentid<@maxid begin if not exists(select empid from dbo.EmployeesExample where EmpID=right('0000'+cast(@currentid as varchar(4)),4)) insert #MissingIDS select right('0000'+cast(@currentid as varchar(4)),4) select @currentid=@currentid+1 end --And what you have left is the missing ids select * from #MissingIDS drop table #MissingIDS
For More Information
- Dozens more answers to tough SQL Server questions from Tony Bain are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Microsoft SQL Server Installation
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our SQL Server experts
View all SQL Server questions and answers
Start the conversation
0 comments