The SELECT statement would use the random number in conjunction with a CASE statement to pick from two different values to insert into a field, in effect tagging records randomly with one value or the other.
My first attempt involved trying to grab the one's digit of the second of today's date via the getdate() function, and based on whether this value is even or odd, pick one or another of the values to assign to a field for the record I insert.
INSERT INTO @MyTableVariable (MyField)
SELECT
CASE
WHEN RIGHT(DATEPART(s, getdate()), 1) IN (0, 2, 4, 6, 8) THEN 32
WHEN RIGHT(DATEPART(s, getdate()), 1) IN (1, 3, 5, 7, 9) THEN 39
END AS MyField
FROM dbo.MyTable t
The result wasn't quite what I expected. Instead of one of each inserted value appearing randomly in the resulting table variable for MyField, the same value appeared each and every time.
As it turns out, I didn't realize that in the CASE statement, the seconds portion of the current time, the "seed" if you will, only got set once at the beginning of the SELECT. There is nothing in the statement to prompt the function to update the current time with every iteration, every record examined from MyTable, it only performs a one-off examination of the seconds and goes with that for the rest of the operation.
My solution involved using instead the one's digit of the integer primary key of the source table, like so.
INSERT INTO @MyTableVariable (MyField)
SELECT
CASE
WHEN RIGHT(t.ID, 1) IN (0, 2, 4, 6, 8) THEN 32 -- Even
WHEN RIGHT(t.ID, 1) IN (1, 3, 5, 7, 9) THEN 39 -- Odd
END AS MyField
FROM dbo.MyTable t
In this case, the subset of data that I'm querying from MyTable is just that, a subset, which means that the primary key (ID) won't be in any particular order; there are plenty of opportunities for the one's digit to be odd or even. This provides a reasonably random sampling of records in the resulting table variable.
No comments:
Post a Comment