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 
