SQL Server choose at random from giving data

Question

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

Answer 1

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.

Answer 2

You mean how to pick a random row from the data? This is one way to do it:

declare @data table (
  value int
)

insert into @data values (16), (17), (18), (22), (24)

select top 1 value from @data order by newid()