Building Sample Data in a table

Question

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?

Answer 1

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

Answer 2

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