Update a Column with Random Numbers in SQL Server

In this blog post, I will show you how to update a database column with Random Numbers

Let’s create a sample table

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);

Observe that the ‘DayAlloted’ column has 0. To allocate random numbers in that column, fire this query:

UPDATE @TT
SET DayAlloted = CONVERT(smallint, RAND(CHECKSUM(NEWID())) * 30)

OUTPUT