SQL: Choose between 2 strings randomly for new column

Question

I am assigning new emails to a mailbox database by creating a table to import. I have a script to create a table with 2 columns, one with new emails and one with the name of our mailbox database. Since our last big upload, we have added a second mailbox database. I need to be able to assign the new emails randomly.

Currently I have it set up like this:

SELECT
txtEmailAddress AS Email
'Old Database Name' AS 'Database'
FROM TblUsers

I now need the Database column to be a random mixture of 'New Database 1' and 'New Database 2'. I have looked into using RAND() but it doesnt seem to work as I thought it would.

Any help is greatly appreciated.

Thanks, Rob

Edit for Gordon:

@GordonLinoff Yes, tblUsers contains the users details including their email address. There's about 100 new users and I want to be able to split them 50/50 between the 2 mailboxes. the resulting table should look something like this:

    Email          |   Database
--------------------------------------------------------
abc@email.com      |      'New Database 1'
def@email.com      |      'New Database 2'
ghi@email.com      |      'New Database 1'
jkl@email.com      |      'New Database 2'
mno@email.com      |      'New Database 1'
etc

Does this answer your question? [How do I generate random number for each row in a TSQL Select?](https://stackoverflow.com/q/1045138/2029983) — Thom A, Aug 13 '20 at 13:10
Sample data and desired results would really help. Do you want duplicates? Or do you want to prevent duplicates. Is `tblUsers` somehow related to the description in the first paragraph? — Gordon Linoff, Aug 13 '20 at 13:32
@GordonLinoff Yes, tblUsers contains the users details including their email address. I haven't included the WHERE clause which filters out all the old users and only has ones who have been added since the start of the summer. There's about 100 new users and I want to be able to split them 50/50 between the 2 mailboxes. the resulting table should look something like this: — Rob, Aug 14 '20 at 11:48
@Rob . . . You should edit the question to clarify it. Or delete this one and ask a new one. — Gordon Linoff, Aug 14 '20 at 12:45

Zohar Peled · Answer 1 · 2020-08-19T08:11:54.873

One fairly simple way to get a random number in SQL Server is to use CHECKSUM on a NEWID().
The NEWID() function gets a new "fairly random" guid, and the CHECKSUM function calculates a number from that.
Then use IIF (or CASE) to get a different string for each of the different outcome of the following equation: CHECKSUM(NEWID()) % 2 = 0.

First, create and populate sample table (Please save us this step in your future questions):

DECLARE @T As Table
(
    Email varchar(20)
);

INSERT INTO @T (Email) VALUES
('abc@email.com'),
('def@email.com'),
('ghi@email.com'),
('jkl@email.com'),
('mno@email.com');

The query:

SELECT Email, IIF(CHECKSUM(NEWID()) % 2 = 0, 'New Database 1', 'New Database 2') As [Database]
FROM @T;

Results:

Email           Database
abc@email.com   New Database 1
def@email.com   New Database 2
ghi@email.com   New Database 2
jkl@email.com   New Database 1
mno@email.com   New Database 1

SQL: Choose between 2 strings randomly for new column

1 Answers1