Return to Revisions

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