| Author |
Topic |
|
flamz
Starting Member
21 Posts |
 Posted - 2008-02-27 : 12:26:29
|
| Hello,I have a table that holds historical data. I am currently debugging an application that reads this data and would like to work with some *fake*, generated historical data.Here's my problem:Each row in the historical table has a date, an ID and a list of values, like this:DATE ID, val1, val2, ..., val302/27/2008 1, 15, 18, ..., 402/27/2008 2, 25, 16, ..., 2I would like to generate new rows for each ID. Each new insert should DECREMENT all values in the record by 1 day for the date and by a random number for the others (except ID, of course). This way, I would end up with something like:DATE ID, val1, val2, ..., val302/27/2008 1, 15, 18, ..., 402/26/2008 1, 14, 17, ..., 202/25/2008 1, 12, 15, ..., 002/24/2008 1, 7, 14, ..., 0...I wrote a program in C that does this, but it's very slow... (ie. I need to generate hundreds of thousands of rows). It uses the brute force approach: get the last historical data, read each field, decrement each one then insert a new record.I think it may be possible to increase the speed by using some clever SQL, but I would need to some with this.Any help is appreciated,Cheers. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-27 : 12:44:29
|
| INSERT INTO HistoryTableSELECT DATEADD(d,-1 * b.number,a.DATE), a.ID, a.Val1-RandNo, a.Val2-RandNo, .... FROM HistoryTable aCROSS JOIN master..spt_values bWHERE b.type='p'AND b.number BETWEEN 1 and 20RandNo is random number generated using one of methods from herehttp://weblogs.sqlteam.com/jeffs/archive/2004/11/22/2927.aspx |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-02-28 : 02:25:22
|
| http://sqlblogcasts.com/blogs/madhivanan/archive/2007/10/10/generating-random-numbers-part-ii.aspxMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
Injecting fake historical data