Sql-server – SQL Server choose at random from giving data

randomsql server

My problem is that I don't know how to choose the random data from a giving set. For example: I have a Node column which need to produce different inserts but only from these numbers: 16, 17, 18, 22, 24. I have looked at many different posts but couldn't find what I'm looking for. Any tips, hints or very simple sample query would be much appreciate.

I'm using SQL Server Management Studio 2014

Best Answer

If you want to generate rows, you could cross join your sample numbers with another dataset, something like this:

--INSERT INTO yourTable ...
SELECT x.y, GETDATE() AS dateAdded, SUSER_NAME() AS addedBy
FROM  
    (
    VALUES ( 16 ), ( 17 ), ( 18 ), ( 22 ), ( 24 )
    ) x(y)
    CROSS JOIN ( SELECT TOP 3 1 z FROM master..spt_values ) v

This will generate 3 rows for each value from your set. Customise as you need.

Related Question