|
|
Posted by David Portas on 10/02/21 11:44
Ramaarneh wrote:
> Hi,
> I want to create a random unique auto generated number. dont want
> to use GUIDs. Any other way to create it in isert statement itself,
> apart from storing a number in a table and increment while inserting.
> Help me!
Let's assume you can create a table of numbers from 0 upto the largest
number you need.
CREATE TABLE numbers (num INTEGER NOT NULL PRIMARY KEY);
INSERT INTO numbers VALUES (0)
WHILE (SELECT MAX(num) FROM numbers)<32768
INSERT INTO numbers
SELECT num+(SELECT MAX(num)+1 FROM numbers)
FROM numbers ;
Now you can easily populate another table with random selections from
your Numbers table:
CREATE TABLE your_tbl (num INTEGER NOT NULL PRIMARY KEY);
INSERT INTO your_tbl (num)
SELECT TOP 1 N.num
FROM numbers AS N
LEFT JOIN your_tbl AS T
ON N.num = T.num
WHERE T.num IS NULL
ORDER BY NEWID() ;
The beauty of this is that you can periodically delete the used ones
from Numbers and you can repopulate it as often as you like. True, it
probably won't scale well to millions of rows but it has worked pretty
well for me with smaller data sets.
Hope this helps.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|