CODEWITHSUNDEEP
sqlsql-server
user3094037
user3094037

Reputation: 11

random number for numeric (16,10) datatype

I have a requirement to generate a random number (in place of identity), so I tried these approaches, but while inserting data I met with a primary key violation after a while.

Attempt #1 (old query):

SELECT CONVERT(NUMERIC (14,2), GETDATE()) * rand() 
FROM sometable

Attempt #2 (modified query):

SELECT CONVERT(NUMERIC(14,2), GETDATE()) * RAND(CAST(NEWID() AS VARBINARY)) 
FROM sometable

Note:

The first query is always getting duplicate when we have more than one record to insert.

The second query gets duplicated while inserting the bulk records ex: 5000 records per transaction.

Sample table structure:

CREATE table #test
(
     id numeric(16,10) primary key
)

Query:

INSERT into #test (ID)
   SELECT CONVERT(NUMERIC(14, 2), GETDATE()) * RAND(CAST(NEWID() AS VARBINARY)) 
   FROM sys.objects

SELECT * FROM #test

Requirement:

I need to insert any large number of rows without any primary key violations.

Kindly advise - I am open to any other alternate ideas as well.

Thanks in advance..

Please correct me if my question is not clear enough.

Upvotes: 0

Views: 96

Answers (1)

Codeman
Codeman

Reputation: 12375

Shrink the problem down. If you were generating a number from 1 to 10, how long would it take to get a duplicate? The problem you're facing is no different, except with a larger problem set.

If you're using something as a generated primary key, you should use an identity column or a GUID. That or a natural key like a SKU or PID or UPC, for example, if you're cataloging products.

Something like this:

INSERT into #test (ID)
  SELECT NEWID()

Upvotes: 1

Related Questions