Random Data Generator
Sometimes, I find that I have need to generate a data set of random integers or characters. I created the following query to generate a data set containing a random set of data for various data types (integer, float, decimal, varchar, bit and datetime). For integers you can input a minimum and maximum value to be generated.
The query is well commented and is easy enough to alter for your specific needs.
I use a Numbers (i.e Tally) table to generate the appropriate number of rows needed. If you don't have a Numbers table simply change the FROM clause to:
FROM sys.columns A CROSS JOIN sys.columns B CROSS JOIN sys.columns C
I hope some of you find this helpful.
Peter Wehner
DECLARE @min INT, @max INT, @rowCnt INT
SET @min = 1;
SET @max = 1000;
SET @rowCnt = 1000;
SELECT TOP (@rowCnt)
--ints
RandInt = ABS(CHECKSUM(NEWID())), -- Random integer
RandIntMinMaxInc = (ABS(CHECKSUM(NEWID())) % (@max - @min + 1)) + @min, -- @min & @max inclusive
RandIntMinIncMaxExc = (ABS(CHECKSUM(NEWID())) % (@max - @min)) + @min, -- @min inclusive & @max exclusive
RandIntMinExcMaxInc = (ABS(CHECKSUM(NEWID())) % (@max - @min)) + @min + 1, -- @min exclusive & @max inclusive
RandIntMinMaxExc = (ABS(CHECKSUM(NEWID())) % (@max - @min - 1)) + @min + 1, -- @min exclusive & @max exclusive
--decimals/floats
RandFloatMinMaxExc = RAND(CHECKSUM(NEWID())) * (@max - @min ) + @min, -- min & max exlusive
RandDecMinMaxExc = CONVERT(DECIMAL(11,2), RAND(CHECKSUM(NEWID())) * (@max - @min ) + @min),-- min & max exlusive (set presicion & scale appropriately)
-- DateTime (3,012,153 is max # of days for datetime, 3,652,058 max days for datetime2)
RandDate = DATEADD(DAY, ABS(CHECKSUM(NEWID())) % (3012153 + 1), CONVERT(DATETIME, '17530101')), -- Datetime (min = 1753-01-01, max = 9999-12-31)
RandDate2 = DATEADD(DAY, ABS(CHECKSUM(NEWID())) % (3652058 + 1), CONVERT(DATETIME2, '00010101')), -- Datetime2 (min = 0001-01-01, max = 9999-12-31)
-- Bit
RandBit = CONVERT(BIT, ROUND(RAND(CHECKSUM(NEWID())), 0)),
-- varchar
RandLetter = SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),
-- replicate a random letter a random number of times between @min and @max
RandString = CONVERT(VARCHAR(MAX), REPLICATE(SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),(ABS(CHECKSUM(NEWID())) % (@max - @min + 1)) + @min))
FROM dbo.Numbers
--FROM sys.columns A CROSS JOIN sys.columns B CROSS JOIN sys.columns C
