0

I need to insert random data in my database for testing, and would need to generate a numeric string (can start with 0) 27 characters long.

I've been looking into NEWID() but it contains also letters, same for NEWSEQUENTIALID().

So far my approach would be to make a while loop, generate each digit randomly and concatenate it, but it seems to be a very slow approach.

I am using MSSQL 2014.

sharkyenergy
  • 3,842
  • 10
  • 46
  • 97
  • Does [this](https://stackoverflow.com/questions/1045138/how-do-i-generate-a-random-number-for-each-row-in-a-t-sql-select) answer your question? – Zhorov Nov 19 '21 at 06:54
  • @Zhorov, no because it generates a single small integer that is up to 2 characters long. not 27. – sharkyenergy Nov 19 '21 at 06:59
  • select it from a `TALLY` table with the required number of digits and `STRING_AGG()` it – Squirrel Nov 19 '21 at 07:03
  • 1
    I'll offer this, but not as an answer, since then I'd be required to explain and defend it -- `select right(replicate('0', 27) + convert(decimal(38, 0), 0x26000001 + crypt_gen_random(13)), 27)`. This approach cannot generate arbitrarily long values, but it's likely to be faster than anything else (where such things matter). – Jeroen Mostert Nov 19 '21 at 08:05

1 Answers1

3

A possible solution, based on this approach (using CHECKSUM() and NEWID()):

CREATE TABLE TestTable(RandomNumber varchar(27))
DECLARE @length int = 27

;WITH rCTE AS (
   SELECT 1 AS n 
   UNION ALL 
   SELECT n + 1 
   FROM rCTE 
   WHERE n < @length
)
INSERT INTO TestTable(RandomNumber)
VALUES ((SELECT ABS(CHECKSUM(NEWID())) % 10 FROM rCTE FOR XML PATH('')))
-- For SQL Server 2017+
-- VALUES ((SELECT STRING_AGG(ABS(CHECKSUM(NEWID())) % 10, '') FROM rCTE))
Zhorov
  • 28,486
  • 6
  • 27
  • 52
  • Thank you, unfortunately I am on MsSql 2014 and that does not have the STRING_AGG function. I add this information to the post – sharkyenergy Nov 19 '21 at 07:12
  • thank you, but how can I add this result to a insert statement? I tried but got the message that FOR XML is not allowed in an insert statement. – sharkyenergy Nov 19 '21 at 07:32