What's an easy way to populate a database through an sql query from a single entry?

Question

We're looking to populate a number of tables for testing. The data can all be the same except for a timestamp column. Other columns in the row contain complicated data, like objects in xml format and other messy stuff, so we don't want to have to remake it. What would be an easy way to expand a single entry to thousands, by incrementing a timestamp by a constant interval, through a sql query.

Our current idea (in a C-ish pseudocode) is to:

Get the latest (likely only) row and store it in a variable "thisRow"
While(thisRow->time < endTime)
{
Increment thisRow->time by a constant variable
Insert thisRow
}

We are using postgres

Answer 1

I like to keep a Numbers table in my databases for certain queries. If you had one then you could use the single statement below. If you don't have one, it's easy enough to generate one as a temporary table or permanent table.

INSERT INTO Test_Table
(
     col1,
     col2,
     ...,
     my_timestamp
)
SELECT
     ST.col1,
     ST.col2,
     ...,
     DATEADD(mi, N.number, ST.my_timestamp)
FROM
     Source_Table ST
INNER JOIN Numbers N ON
     N.number BETWEEN 1 AND 1000  -- Change this to what you want

This is for SQL Server. The DATEADD function may be different in your RDBMS. You can also add in a random function if you don't want the intervals to be exactly one minute and you could of course change the intervals to be hours, days, or whatever. You could also use a simple equation if you wanted say two hours per interval.

Answer 2

Using SQL SERVER 2005+ you can make use of CTE statements

Have a look at this

DECLARE @StartTime DATETIME,
     @EndTime DATETIME

SELECT  @StartTime = '01 Jan 2009',
     @EndTime = '31 Jan 2009'

DECLARE @Table TABLE(
     DateVal DATETIME,
     Col1 INT
)

INSERT INTO @Table SELECT @StartTime, 1

SELECT  *
FROM    @Table

;WITH CTE AS (
     SELECT @StartTime StartTime
     UNION ALL
     SELECT DATEADD(dd, 1, StartTime) StartTime
     FROM CTE 
     WHERE StartTime < @EndTime
)
SELECT  CTE.*,
     t.Col1
FROM    CTE,
     @Table t

Answer 3

If you are using a database that supports recursion, for example Microsoft SQL Server, you can use something like:

WITH T AS (
    SELECT 1 AS r
    UNION ALL
    SELECT r + 1 AS r
    FROM T
    WHERE r < 100 -- Or however many rows you need
)
INSERT INTO TestTable (Col1, Col2,...)
SELECT s.Col1, s.Col2,...
FROM SourceTable s
    CROSS JOIN T