Add random number to table, no duplicates, most efficient

[MagnusFox]

I am creating 12 character randomly generated "Certificate Codes" for security. I need to add these codes to a table and ensure no duplicates in monthly batches of 10,000 codes (each month adding 10,000 new unique codes).

What is the most efficient way to add these codes to the table while ensuring that it is not a duplicate? If the code is a duplicate, how do I skip that code while ensuring the batch still has 10,000 new unique codes created?

Please follow the link below to what I have created so far. It seems like there should be a better way to do the adding of the new certificate code. As my table grows with valid certificate codes, the lookup for duplicates will take longer and longer. I would appreciate any comments on the best way to attack this issue. If you would do something different, let me know.

http://www.omnidelta.com/random/codes.txt

(ASP code)

Thanks,
Magnus

 

[jfrost10]

Hey Magnus,

what db are you using? If you're using SQL Server 2000, it has a built in datatype called "UniqueIdentifier". It creates a type GUID that is gauranteed to be unique. You may want to play with it a bit (its longer than 12, and you can't treat it/convert it like a string, so I'm not sure if you can cut it off after 12), but it will be unique. Also, you can let sql server handle it and reduce your code.

If you aren't using sql server 2000, I know that in Visual Basic there are ways of creating a GUID manually, so you may want to look through msdn help to figure that out. It's usually a combination of userid, month, date, second, etc. etc.

Good luck!

Jack

 

[MagnusFox]

Jack,

The DB is Access to start. I have seen the code for GUID, while interesting, I want to creaet a 12 charecter code that uses only uppercase letters. The GUID may contain numbers, so used that code and reduced what it writes for the string (follow the link in the original post).

Any help on making my code better? Faster?