Insert N rows with date interval

Question

I need to insert rows into a database, where every row is the same except a date column which should have its date incremented by 1 week for each new row. So, basically this:

  for(n = 0; n<X; n++)
      insert into events (date, title) values (start_date + 7*n, 'static title');

Any MySQL trick that can be used to do this?

Answer 1

You can use:

SELECT
 'static_title' AS title,
 DATE_ADD(@start_date, INTERVAL @i:=@i+1 WEEK) AS result_date
FROM
 (SELECT
 (two_1.id + two_2.id + two_4.id + 
 two_8.id + two_16.id) AS id
 FROM
 (SELECT 0 AS id UNION ALL SELECT 1 AS id) AS two_1
 CROSS JOIN (SELECT 0 id UNION ALL SELECT 2 id) AS two_2
 CROSS JOIN (SELECT 0 id UNION ALL SELECT 4 id) AS two_4
 CROSS JOIN (SELECT 0 id UNION ALL SELECT 8 id) AS two_8
 CROSS JOIN (SELECT 0 id UNION ALL SELECT 16 id) AS two_16
 ) AS sequence
 CROSS JOIN
-- @i:=0 for not including current week
 (SELECT @i:=-1, @start_date:=CURDATE()) AS init
WHERE
  sequence.id<10;

-that will produce N rows (here N=10). To insert rows, just use INSERT .. SELECT syntax. Fiddle is here. Also in sample start_date is set to CURDATE() - but you can easily adjust that in query, of course.