I'm trying to randomly insert values from a list of pre-defined values into a table for testing. I tried using the solution found on this StackOverflow question:
stackoverflow.com/.../update-sql-table-with-random-value-from-other-table
When I I tried this, all of my "random" values that are inserted are exactly the same for all 3000 records.
When I run the part of the query that actually selects the random row, it does select a random record every time I run it by hand, so I know the query works. My best guesses as to what is happening are:
- SQL Server is optimizing the
SELECT
somehow, not allowing the subquery to be evaluated more than once - The random value's seed is the same on every record the query updates
I'm stuck on what my options are. Am I doing something wrong, or is there another way I should be doing this?
This is the code I'm using:
DECLARE @randomStuff TABLE ([id] INT, [val] VARCHAR(100))
INSERT INTO @randomStuff ([id], [val])
VALUES ( 1, 'Test Value 1' )
INSERT INTO @randomStuff ([id], [val])
VALUES ( 2, 'Test Value 2' )
INSERT INTO @randomStuff ([id], [val])
VALUES ( 3, 'Test Value 3' )
INSERT INTO @randomStuff ([id], [val])
VALUES ( 4, 'Test Value 4' )
INSERT INTO @randomStuff ([id], [val])
VALUES ( 5, 'Test Value 5' )
INSERT INTO @randomStuff ([id], [val])
VALUES ( 6, null )
INSERT INTO @randomStuff ([id], [val])
VALUES ( 7, null )
INSERT INTO @randomStuff ([id], [val])
VALUES ( 8, null )
INSERT INTO @randomStuff ([id], [val])
VALUES ( 9, null )
INSERT INTO @randomStuff ([id], [val])
VALUES ( 10, null )
UPDATE MyTable
SET MyColumn = (SELECT TOP 1 [val] FROM @randomStuff ORDER BY NEWID())