Update a SQL Server Column with Random Numbers within a Range

Some time back, I had shared some code to Update a Column with Random Numbers in SQL Server

A SqlServerCurry reader mailed back asking me if it was possible to generate random numbers between a minimum and maximum range. Here’s a sample query that does that:

Test Data

DECLARE @TT table
(
ID smallint,
StudentID smallint,
DayAlloted smallint
)
-- Create Sample Data
INSERT INTO @TT VALUES ( 101, 1, 0);
INSERT INTO @TT VALUES ( 102, 2, 0);
INSERT INTO @TT VALUES ( 103, 3, 0);
INSERT INTO @TT VALUES ( 104, 4, 0);
INSERT INTO @TT VALUES ( 105, 5, 0);
INSERT INTO @TT VALUES ( 106, 6, 0);
INSERT INTO @TT VALUES ( 107, 7, 0);
INSERT INTO @TT VALUES ( 108, 8, 0);
INSERT INTO @TT VALUES ( 109, 9, 0);
INSERT INTO @TT VALUES ( 110, 10, 0);

UPDATE @TT
SET DayAlloted = ABS(CHECKSUM(NEWID())) % 10 + 1

SELECT * FROM @TT

The query generates a random number between 1 and 10.

Similarly to generate a random number between 24 and 123, use this query:

UPDATE @TT
SET DayAlloted = ABS(CHECKSUM(NEWID())) % 100 + 24

There are other ways to generate the random number within a range and this is just one of them. If you too have a script, please share it in the comments section.

Did you like this post?

	subscribe via rss		subscribe via e-mail
	print this post		follow me on twitter

Feedback: