help me generate sample data using sql script

Question

I would like to write the SQL (MS SQL 2005/08) script to insert random large number of data in the database to test the system again heavy data.

Now consider that i have sample of data like below for some columns

stats [fail, pending, success, done, successful, partial_done]
date [range preferably two end dates]

Now can i write SQL script which can pick the random entry for these columns from range and can help me create realistic random data instead of repeating only single entry ?

Answer 1

If you store the sample data in a table:

INSERT INTO table (COL1, COL2, COL3) VALUES
(SELECT COL1 from sample order by RAND() LIMIT 1),
(SELECT COL2 from sample order by RAND() LIMIT 1),
(SELECT COL3 from sample order by RAND() LIMIT 1)

Answer 2

The accepted answer for this question looks somewhat inappropriate.

Assuming the assumption about sample data is correct then a way that will insert more than 1 row at a time and actually work in SQL Server is as follows.

INSERT INTO table (COL1, COL2, COL3) 
  SELECT TOP 1000 s1.COL1, s2.COL2, s3.COL3
  FROM sample s1, sample s2, sample s3
   ORDER BY NEWID()