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 ?
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)
VALUES clause and 'RAND()' evaluates as a constant value for all rows.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()
