Bohdan Bohdan - 10 months ago 39
SQL Question

TSQL: How to generate a random table of certain size in SQL Server?

Let say we need to write stored procedure that returns couple character fields and a number.

A - nvarchar
B - nvarchar
C - float

Is there a way to generate random table of predefined size in this stored procedure?

For example if size required is 5 than legit result ideally would be:

random string, random string 2, random number 1
random string 3, random string 4, random number 2
random string 5, random string 6, random number 3
random string 7, random string 8, random number 4
random string 9, random string 10, random number 5

Where random string would ideally satisfy some pattern.

Answer Source

I'd need more details about each string and number but this should get you started:

CREATE PROC dbo.getRandomStuff (@rows int)
E1 AS (SELECT v FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(v)),
EX AS (SELECT v=1 FROM E1 a, E1 b, E1 c, E1 d, E1 e)
SELECT TOP (@rows) 
  string1 = newid(), 
  string2 = newid(),
  Num1    = ABS(CHECKSUM(newid())%1000)+1

and you would use it like this (here's 5 rows):

EXEC dbo.getRandomStuff 5;