SQL OUTER JOIN with NEWID to generate random data for each row

Question

Hi, I want to generate some test data so for each row in a table I want to insert 10 random rows in another, see below:

INSERT INTO CarFeatures 
  (carID, featureID)
SELECT C.ID, F.ID
  FROM dbo.Cars AS C
OUTER APPLY (
     SELECT TOP 10 ID
       FROM dbo.Features
   ORDER BY NEWID()
) AS F

Only trouble is this returns the same values for each row. How do I order them randomly?

Answer 1

The problem is that any function you call will be evaluated only once. How about something like this:

SELECT ID, NEWID() AS guid
INTO #temp
FROM dbo.Features

INSERT INTO CarFeatures (carID, featureID) 
SELECT C.ID, F.ID 
FROM dbo.Cars AS C 
OUTER APPLY ( 
SELECT TOP 10 *
FROM #temp
ORDER BY 2
) AS F 

Answer 2

What i usually do is create a temp table and define the PK as a GUID with the default value of newid(). You'll need a create table statement for this, no select into. Then I insert my records into it and then I can order by the Id field and select the top ten.