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


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
    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.


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()
  • Sorry for that. Not the rows, i need to produce the inserts from these numbers at random. Normal insert statement to put it in a loop. – Jakubee Feb 20 '15 at 17:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.