Building Sample Data in a table

vsslasd 466 Reputation points
2020-09-03T21:57:19.69+00:00

I'm trying to seed sample data into a table. I've created a cross join query which combines two tables and works.

However, I'd like to insert a lookup column that selects a random value from the vendorid column, so I have various random values to insert.

Here is my query:

 Select 
 A.Invoice,
 B.PO,
 VendorId=
 (SELECT  TOP 1 VENDORID
FROM Vendors
TABLESAMPLE (1000 ROWS)
ORDER BY NEWID())
 From APTrans A 
 Cross Join APTemplate B

Everything works as expected, except the VendorId. It only returns one VendorId for each row returned. How can I get a random VendorId for each row returned in my query?

No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,126 Reputation points Microsoft Employee
    2020-09-04T02:01:43.047+00:00

    Hi @vsslasd ,

    Please have a try with below and check whether it is helpful to you. Thanks.

      Select   
      A.Invoice,  
      B.PO,  
      VendorId=  
      (SELECT  TOP 1 VENDORID  
     FROM Vendors  
     TABLESAMPLE (1000 ROWS)  
     WHERE     A.Invoice = A.Invoice and B.po=B.po  
     ORDER BY NEWID())  
      From APTrans A   
      Cross Join APTemplate B  
    

    OR:

    SELECT      
    A.Invoice,  
    B.PO,  
    C.VendorId  
    FROM    APTrans A  
    CROSS APPLY  APTemplate B  
    CROSS APPLY ( SELECT TOP 1 VendorId  
                          FROM      Vendors TABLESAMPLE (1000 ROWS)  
                          WHERE     A.Invoice = A.Invoice and B.po=B.po  
                          ORDER BY  NEWID()  
                        ) C  
    

    If the response is helpful, please click "Accept Answer" and upvote it.

    Best regards
    Melissa

    No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 68,206 Reputation points Microsoft MVP
    2020-09-04T10:02:40.297+00:00

    Have you tried removing the TABLESAMPLE clause? I don't have much faith in that one.

    No comments