Click here to Skip to main content
15,904,156 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i need
insert unique value and between 1 and 3000 sql server


how to do it\?

SQL
declare @st int=1
declare @rn int
while(@st <= 3000)
 begin
 set @rn=  ABS(Checksum(NewId()) % 3000);
 set  @st = @st+1;
 insert into HumanResourceSchema.Employee (EmpNo,EmpFname,EmpLname,DeptNo)values(@rn,'Smith','Jane','d1')
 end


the error say

Violation of UNIQUE KEY constraint 'c3'. Cannot insert duplicate key in object 'HumanResourceSchema.Employee'. The duplicate key value is (<NULL>)
Posted
Updated 9-Dec-14 10:06am
v2
Comments
Maciej Los 9-Dec-14 16:14pm    
How many values you want to add?
TheSniper105 9-Dec-14 16:23pm    
3000
Maciej Los 9-Dec-14 16:52pm    
If the number of uniques is equal to the number of records, why to use random function?
If you want to add 3000 unique random numbers, the range of randomized numbers must be much, much bigger (for example: 10,000).
Zoltán Zörgő 9-Dec-14 17:08pm    
Just to be precise: 3000 random numbers from 1 to 3000 is called "permutation".

1 solution

Please, read my comment to the question. Here is an idea: SQL: Generate random integers without collisions[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900