Automation SQL - get random value for each record [closed]

Question

I need to populate some Data Extension using another Data Extension that contains all the accounts data. I want to attach a new column and the value for that column should be randomized from another Data Extension that contains some data. I've encountered a problem with putting the randomized value as an additional column - it randomizes the value but it puts the same value for all the records. I want to randomize different values for different records.

Table example with values that should be randomized:

Table name: Newsletter subscribers - data

id	value
1	media
2	internet
3	life

and here is my SQL query:

SELECT *,
(SELECT TOP 1 value FROM [Newsletter subscribers - data] ORDER BY NEWID()) AS Content

FROM [Account]

I've tried also ORDER BY RAND() but it doesn't work as well...

Answer 1

You are trying to join to tables without a common key. This can be achieved by calculating a ROW_NUMBER() for each row, and joining the two DEs on rowNumber. This isn't random yet but will keep the order of both DEs.

So: If you only need to perform this once, you could randomize one of your DEs by creating a Data Extension of type "random", "splitting" it into resulting DE with 100% of records, which will physically randomize the order of rows.

then you can just run this.

SELECT 
sub1.fieldInDE1
,sub2.fieldInDE2 
FROM (
   SELECT deOne.fieldInDE1,
          ROW_NUMBER() OVER (ORDER BY deOne.fieldInDE1) AS rn
   FROM myTable1 deOne
   ) AS sub1
INNER JOIN  (
   SELECT fieldInDE2,
          ROW_NUMBER() OVER (ORDER BY fieldInDE2) AS rn
   FROM myTable2 deTwo
   ) AS sub2
ON sub1.rn = sub2.rn

If you need to do this regularly or want to have it as pure SQL, then combine the above with an ORDER BY NEWID() in one of the tables, which will randomize one of the DEs on-the-fly:

SELECT 
sub1.fieldInDE1
,sub2.fieldInDE2 
FROM (
   SELECT deOne.fieldInDE1,
          ROW_NUMBER() OVER (ORDER BY deOne.fieldInDE1) AS rn
   FROM myTable1 deOne
   ) AS sub1
INNER JOIN  (
   SELECT deTwo.fieldInDE2,
          ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn
   FROM myTable2 deTwo
   ) AS sub2
ON sub1.rn = sub2.rn

Edit: as per your comment: If your tables arent equal in length, then try:

Cross apply the long with the short table. This will give you what you want, but in multiples of the number of rows of the short table. In your case, the expected result, but 3 times.

Hence, deduplicate the result using ROW_NUMBER() / OVER / PARTITION BY, returning only values where Row_number = 1. In this process, we also randomize one table using ORDER BY NEWID()

SELECT
a.fieldNameLongTable 
,a.fieldNameShortTable 
FROM 
    (SELECT
        t1.fieldNameLongTable
        ,t2.fieldNameShortTable
        ,ROW_NUMBER() OVER(Partition by t1.fieldNameLongTable ORDER BY NEWID()) AS Row_Number
        FROM longTable t1
        
        CROSS APPLY
        (SELECT fieldNameShortTable
        FROM shortTable
        ) t2
    ) a
WHERE a.Row_number = 1