Select Random Rows From Multiple Tables In One Query

I'm trying to insert some dummy data into a table (A), for which I need the IDs from two other tables (B and C). How can I get n rows with a random B.Id and a random C.Id. I've got

Solution 1:

If you want to avoid duplicates, you can use row_number() to enumerate the values in each table (randomly) and then join them:

select b.id as b_id, c.id as c_id
from (select b.*, row_number() over (orderby newid()) as seqnum
      from b
     ) b join
     (select c.*, row_number() over (orderby newid()) as seqnum
      from c
     ) c
     on b.seqnum = c.seqnum;

You can just add top N or where seqnum <= N to limit the number.

Solution 2:

If I'm reading your question correctly, I think you want N random rows from the union of the two tables - so on any given execution you will get X rows from table B and N-X rows from table C. To accomplish this, you first UNION tables B and C together, then ORDER BY the random value generated by NEWID() while pulling your overall TOP N.

SELECT TOP 50--or however many you like
    DerivedUnionOfTwoTables.[ID],
    DerivedUnionOfTwoTables.[Source]
FROM 
    (
    (SELECT NEWID() AS [Random ID], [ID], 'Table B'AS [Source] FROM B)
    UNIONALL
    (SELECT NEWID() AS [Random ID], [ID], 'Table C'AS [Source] FROM C)
    ) DerivedUnionOfTwoTables
ORDERBY 
    [Random ID] DESC

I included a column showing which source table any given record comes from so you could see the distribution of the two table sources changing each time it is executed. If you don't need it and/or don't care to verify, simply comment it out from the topmost select.

Solution 3:

You shouldn't need to join to a large table - Select top N ID from B order by newid() should work as newid() works per-row (unlike RAND()). Your join is probably doing a cross-join which will give you multiple results for each newid value.