Generate random number for each row

Today I had to add a new date column to one of my table, and populate it with some random entries. I found a script from SQL Team Blog about generating random numbers. I modified it a little and used it to populate these dates based on another date I had in my table.

Here is modified code:

-- Create the variables for the random number generation
DECLARE @Upper int;
DECLARE @Lower int-- This will create a random number between 1 and 365
SET @Lower = 1 -- The lowest random number
SET @Upper = 365 -- The highest random number

-- we'll use this temp table to assign a random number to each ID value
CREATE TABLE #temp (ID int NOT NULL, RandNum float NULL)
INSERT #temp (ID) SELECT ID FROM FOO

-- now, assign a new random value to each key value in #temp
DECLARE @id int
DECLARE Randomizer CURSOR
FOR SELECT RandNum FROM #temp

OPEN Randomizer
FETCH NEXT FROM Randomizer INTO @id

WHILE @@Fetch_Status != -1
BEGIN
UPDATE #temp SET RandNum = rand()
WHERE CURRENT OF Randomizer

FETCH NEXT FROM Randomizer
END

CLOSE Randomizer
DEALLOCATE Randomizer

UPDATE F
SET F.NewDate = dateadd(dd, round((T.RandNum * 100), 0), F.AnyDate)
FROM
FOO F INNER JOIN #temp T ON F.ID = T.ID

DROP TABLE #temp

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.