CODEWITHSUNDEEP
sqlsql-server-2008
CRoshanLG
CRoshanLG

Reputation: 498

Populate table with random data

I have two tables as following.


'Areas' Table

|    AreaKey      |    AreaID    |
|-----------------|--------------|
| <identity/int>  |  <varchar>   |


'Readings' Table

|    ReadingKey   |      AreaKey      |   Reading   |    ReadingDateTime   |
|-----------------|-------------------|-------------|----------------------|
| <identity/int>  |<FK:AreaKey-Areas> |   <float>   |      <datetime>      |


Areas table already have some data with row id's ranging from 1 to 50.

I want to populate the Readings table with some sample data - (random float values for 'Reading' column between 1.0 and 100.0 AND a random datetime value for ReadingDateTime between a given DateTime range; for example between current datetime and a datetime 3 months back). These values should be inserted to the Reading table by randomly selecting AreaKeys which already exist in the Areas table.


In other words I want to insert random reading values to randomly selected areas, with random datetimes.

Can anyone give me a clue on how to do this?

Upvotes: 3

Views: 1696

Answers (3)

sgeddes
sgeddes

Reputation: 62861

Assuming your Areas table has 50 records, with row ids 1-50, I would just look into using the RAND function.

Something like this seems to work:

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.

Upvotes: 2

David Montgomery
David Montgomery

Reputation: 1648

Have you taken a look at RedGate's SQL Data Generator? RedGate tools have been a total boon for our company.

Tool recommendations aside, just write a quick application that:

  1. generates a list of keys for your Area table
  2. inserts some randomized records into your Area table
  3. inserts some randomized records into your Readings table, picking random elements from the list of keys you created in step #1

Upvotes: 2

Taicho
Taicho

Reputation: 143

You can use NEWID() and select the results into a temp table. Please check http://msdn.microsoft.com/en-us/library/ms190348.aspx

Select column into #temp from table
order by NEWID()

Upvotes: 1

Related Questions