Insert row for each date within range

Question

In my MySQL database, I want to insert table rows for each day within a specific date range (1 july 2014 to 1 july 2015). Each row has a set of different values, but only two of them need to be set (date & status), the other values should remain NULL.

Is there any way I can achieve this?

Thanks!

Answer 1

If you want to insert the same status for every day in this range, you could use:

INSERT INTO your_table (date, status, col3, col4)
SELECT
    DATE_ADD('2014-07-01', INTERVAL t.n DAY),
    'your status',
    NULL,
    NULL
FROM (
    SELECT 
        a.N + b.N * 10 + c.N * 100 AS n
    FROM
        (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
       ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
       ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) c
    ORDER BY n
) t   
WHERE
    t.n <= TIMESTAMPDIFF(DAY, '2014-07-01', '2015-07-01');

Explanation

The subselect creates a temporary table with a row for every day between start ('2014-07-01') and end ('2015-07-01'). So MySQL has only to do one single INSERT and this is much faster than a row by row insert.