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

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:

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



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

1 comment:

Madhivanan said...

Other methods