Populating a table with random data
I have two tables:
Areas table
| AreaKey | AreaID |
|-----------------|--------------|
| <identity/int> | <varchar> |
Table "Indications"
| ReadingKey | AreaKey | Reading | ReadingDateTime |
|-----------------|-------------------|-------------|----------------------|
| <identity/int> |<FK:AreaKey-Areas> | <float> | <datetime> |
- The "AreaKey" in the Readings table is a foreign key in the AreaKey table from Areas.
There is already some data in the scopes table with row id from 1 to 50.
I want to populate the "Readings" table with some sample data - (random floating point values for the "Read" column between 1.0 and 100.0 and a random datetime value for ReadingDateTime between a given DateTime range, for example between the current time and datetime 3 months ago) ... These values should be inserted into the reading table by accident by selecting AreaKeys that already exist in the Areas table.
In other words, I want to insert random read values into randomly selected areas with random dates.
Can anyone give me an idea on how to do this?
source to share
Assuming the Areas table has 50 records, with row IDs 1-50, I'll just look at using the function RAND
.
It seems to be something like this:
SELECT ROUND(((50 - 1 -1) * RAND() + 1), 0) as AreakKey,
ROUND(((100 - 1 -1) * RAND() + 1), 1) as Reading,
DATEADD(mm,-3,GETDATE()) +
(
ABS(
CAST(
CAST( NewID() AS BINARY(8) ) AS INT
)
)
%
CAST(
(GETDATE() - DATEADD(mm,-3,GETDATE())) AS INT
)
) as ReadingDateTime
And here's some SQL Fiddle .
Good luck.
source to share
Have you taken a look at RedGate SQL Data Generator ? RedGate tools have been a boon for us.
Outline tool recommendations, just write a quick app that:
- generates a list of keys for the Area table.
- inserts some randomized records into the Area table.
- inserts some randomized records into your Readings table, picking random items from the list of keys you created in step # 1
source to share
You can use NEWID () and fetch results in temp table. Check http://msdn.microsoft.com/en-us/library/ms190348.aspx
Select column into #temp from table
order by NEWID()
source to share