jeudi 19 janvier 2017

SQL update column with random numbers from set list of values

I have a table that gets rewritten each night. I have two columns that need to be populated with a random number from a set list of values. Angle column needs to be populated with 15,30,45,60,90 and Distance needs to populated with 9,15,21. The insert statements may process up to 700 records.

I have tried creating a temp table (@MyRandomVal1) with select 15 union select 30 union select 45 etc... then use (select top 1 val from @MyRandomVal1 order by newid()). This populates the column with the same random number for all rows. It seems that I might need to loop through the inserted rows so it runs (select top 1 val from @MyRandomVal1 order by newid()) for each row, however in my research I have read that Loops are not recommended. Is there another method for populating 700+ rows with a random sampling from a set list during an insert? Below is my existing code (for angle only). SQL Server 2012.

DECLARE @MyRandomVal1 Table ( id int identity (1,1), val int not null)

INSERT INTO @MyRandomVal1 (val) SELECT 15 union SELECT 30 union SELECT 45 union SELECT 60 union SELECT 90

INSERT INTO MyTable (AUTO_KEY,E3_KEY,EMPID,ENAME,COLOR,ANGLE) SELECT dbo.getautokey(),dbo.GetAutoKey(),[EMPID],[ENAME],abs(checksum(NewId()) % 256),(select top 1 val from @MyRandomVal1 order by newid()) FROM MyTable2 WHERE [JOBLEVEL]='SVP'

Thanks.




Aucun commentaire:

Enregistrer un commentaire