|
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 #MissingIDS
A 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
|
