Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kekewong
Starting Member
6 Posts |
 Posted - 2010-01-24 : 02:45:42
|
| Hi, i am new to SQL and i'm trying to generate a primary key from range number 10000 to 99999 and return to my program . I able to generate a random number with programming language but i scare that the random number already exits in the table. Any solution for doing this task in SQL ? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-01-24 : 03:12:57
|
[code]WITH n0 AS (SELECT 1 AS p UNION ALL SELECT 1), n1 AS (SELECT 1 AS p FROM n0 AS a CROSS JOIN n0 AS b), n2 AS (SELECT 1 AS p FROM n1 AS a CROSS JOIN n1 AS b), n3 AS (SELECT 1 AS p FROM n2 AS a CROSS JOIN n2 AS b), n4 AS (SELECT 1 AS p FROM n3 AS a CROSS JOIN n3 AS b), n5 AS (SELECT 1 AS p FROM n4 AS a CROSS JOIN n4 AS b)SELECT TOP(1) nFROM ( SELECT TOP (90000) ROW_NUMBER() OVER (ORDER BY p) + 9999 AS n FROM n5 ) AS dLEFT JOIN YourTableNameHere AS y ON y.ID = d.nWHERE y.ID IS NULLORDER BY NEWID()[/code]
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-24 : 04:51:17
|
| "i'm trying to generate a primary key from range number 10000 to 99999 and return to my program"Can you just use a column with IDENTITY attribute? |
|
|
|
kekewong
Starting Member
6 Posts |
Posted - 2010-01-24 : 22:53:54
|
| Thanks Kristen for giving this idea, now it works fine . |
|
|
|
|
|
|
Store Procedure of generating primary key