Wednesday , 24 May 2017
Home » Technology » Development » Random Insert/Select with T-SQL

Random Insert/Select with T-SQL

Here’s something I found pretty useful. The situation is I need to insert entries into the database with one of the columns being some random data. If I did a Rand() directly in the statement:

It will only generate Rand() once and fill in all rows with the same data, which is not what I want.

In order to generate a random number for each row, I have to create a view of the RAND() function:

This works very well. Of course, 29 and 27 are my min and max respectively. I can create a separate function to do the same thing:

So instead, I can do RandNumber(27, 29) within the statement.

The problem why I didn’t use this randomization is because of permissions. Elevating a standard user to allow creation of a view is disallowed in my situation.

So this is what I did instead, to keep within the boundaries of the permissions, is to use row_number() instead. Take the mod of row_number by ((@Max + 1) – @Min) which is 3 for my case and add the @Min to it. It’s not random, but deterministic, but it works well enough for my case.

So that’s it. A deterministic pseudo “random” (not so random it seems) number based on row_number(). I wonder if there are other solutions to this.

About Justin Lee

Check Also

Apple WWDC 2014 Videos Podcast RSS Feed Generator using XSLT

I realised there isn’t any podcast subscription link for the Apple WWDC 2014 videos at …

Leave a Reply