DROP TABLE #MyHead;
WITH
cteRandom AS
( --=== Generate the random number first
 SELECT TOP (1000)
        ID           = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
        RandomColor# = ABS(CHECKSUM(NEWID()))%3
   FROM      sys.all_columns ac1
  CROSS JOIN sys.all_columns ac2
) --=== Now, pick the color according to the generated number
 SELECT ID,
        RandomColor = CASE RandomColor#
                      WHEN 1 THEN 'Red'
                      WHEN 2 THEN 'Green'
                      ELSE 'Yellow'
                      END
   INTO #MyHead
   FROM cteRandom
;
--===== Show the distribution
 SELECT RandomColor, COUNT(*)
   FROM #MyHead
  GROUP BY RandomColor
  ORDER BY RandomColor

DROP TABLE #MyHead;

 SELECT TOP (1000)
        ID           = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
        RandomColor  = CASE RandomColor#
                       WHEN 1 THEN 'Red'
                       WHEN 2 THEN 'Green'
                       ELSE 'Yellow'
                       END
   INTO #MyHead
   FROM      sys.all_columns ac1
  CROSS JOIN sys.all_columns ac2
  CROSS APPLY (SELECT ABS(CHECKSUM(NEWID()))%3) ca (RandomColor#)
;
--===== Show the distribution
 SELECT RandomColor, COUNT(*)
   FROM #MyHead
  GROUP BY RandomColor
  ORDER BY RandomColor
;