How to update many rows with random values

Question

I would like to fulfill an update request to a table and set 2 columns to random values. Here is an example:

Update Network_Info_Detail set ART = (rand()*4000)+2, NRT = (rand()*1000)+2

Obviously, all rows will be updated with the same randomly generated value, so I need to create a loop to generate a random value for each row.

DECLARE @size integer SET @size = (SELECT Count(*) from Network_Info_Detail) While @size > 1 BEGIN Update top (@size) Network_Info_Detail set ART = (rand()*4000)+2, NRT = (rand()*1000)+2 SET @size = @size - 1 END

This script updates lines with different numbers, but very slowly. Is there a way to improve runtime?

Answer 1

You can use the trick SQL Server to generate a random number: rand(checksum(newid())).

Update Network_Info_Detail
    set ART = (rand(checksum(newid()))*4000)+2,
        NRT = (rand(checksum(newid()))*1000)+2;