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

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook