Minah


I want to generate 100 numbers which is not already used in the table. for eg if i have a table employee, with fields empid(int), empname then i want to select some 100 numbers which is not already in table. The purpose is to let user select the ID they want.

Thanks in advance





Re: Select the values which are not already in the table

clintz


try this..

declare @tbl table (empID int, name varchar(5))
insert into @tbl
select 2, 'a' union all
select 21, 'b' union all
select 33, 'c' union all
select 10, 'd' union all
select 13, 'e' union all
select 18, 'f' union all
select 5, 'g'

declare @i int
set @i = 0
declare @t table (RandomEmpID int)
declare @rand int
while @i < 100
begin
select @rand = floor(rand() * 1000)
if (@rand not in (select empID from @tbl))
begin
insert into @t
select @rand
set @i = @i+1
end
end
select * from @t


if you don't want random numbers, you can just increment your max(empID) upto 100..

- clintz






Re: Select the values which are not already in the table

Kent Waldrop Jn07

A more efficient way of generating a list of random numbers might be to use a table of numbers; something like:

Code Snippet

select abs(cast(substring(cast(newid() as varbinary(16)), 13, 4) as int))
from numbers (nolock)
where n <= 100






Re: Select the values which are not already in the table

Arnie Rowland

First, I would create a 'Numbers' table, as large as necessary. Then by using a JOIN with the numbers table it becomes easy to find the 'missing' values.

Code Snippet

DECLARE @Numbers table
( NextNum int )

DECLARE @n int
SET @n = 1
WHILE @n < 100
BEGIN
INSERT INTO @Numbers VALUES ( @n )
SET @n = ( @n + 1 )
END

DECLARE @MyTable table
( EmployeeID int,
LastName varchar(5)
)

INSERT INTO @MyTable VALUES ( 1, 'Bill' )
INSERT INTO @MyTable VALUES ( 3, 'Steve' )
INSERT INTO @MyTable VALUES ( 4, 'Wei' )
INSERT INTO @MyTable VALUES ( 6, 'John' )
INSERT INTO @MyTable VALUES ( 9, 'Li' )

SELECT TOP 10
n.NextNum
FROM @MyTable m
RIGHT JOIN @Numbers n
ON m.EmployeeID = n.NextNum
WHERE LastName IS NULL

NextNum
-----------
2
5
7
8
10
11
12
13
14
15






Re: Select the values which are not already in the table

Kent Waldrop Jn07

Thanks for picking me up Arnie. (I'm asleep at the wheel AGAIN!) You can find a writeup about tables of numbers here:

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html





Re: Select the values which are not already in the table

Minah

Thank you very much

The solution wont give exactly 100 numbers it will give 100 - the noSurprisef records already in employee table.I am still working on it






Re: Select the values which are not already in the table

Minah

what is that n




Re: Select the values which are not already in the table

Arnie Rowland

The suggestion that I posted will provide you with exactly 100 numbers -IF the quantity in the Numbers table is large enough.

For example if your larges EmployeeID in the Employees table goes is about 5000, then the numbers table needs to be at least one hundred rows more than 5000.