Consider creating a Calendar table.
The first table I create in any database I work with, is a table of numbers, 1 to 1000000. Such a table is useful for lots of purposes, like implementing loops in SQL. Also, it can be used to generate the second table I create on any database I work with: The Calendar table.
The calendar table has one row for each date, starting at the first recorded business event in your database (plus a year or so). Keep enough future dates for all relevant business queries (plus a few years to be safe).
Your specific problem can be solved with either of the above tables, but the calendar table approach would be much easier.
I’ll give you a trivial but working example in MySQL below:
create table digits(n int);
insert into digits values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table numbers(
n int not null
,primary key(n)
);
The digits table is just a working table, it will be dropped once the real numbers table has been created. The numbers table has just one column, it’s the primary key. Next, generate 1 million consecutive integers starting at 1. (It sounds slow, but it actually completes in under 10 sec on my 2 year old laptop).
insert
into numbers(n)
select 1
+ (d1.n * 1)
+ (d2.n * 10)
+ (d3.n * 100)
+ (d4.n * 1000)
+ (d5.n * 10000)
+ (d6.n * 100000) as n
from digits d1
,digits d2
,digits d3
,digits d4
,digits d5
,digits d6;
/* Drop the working table. */
drop table digits;
Next, I’ll create a calendar table. Obviously it is quite useless at the moment as it doesn’t have any useful columns. Examples of useful columns are year, monthname, week number, isFirstMonthDay, isLastMonthDay, Financial Year, isHoliday, Holidayname, dayname, quarter, tertial. For non-standard periods, this is golden.
create table calendar(
datum date not null
,primary key(datum)
);
Ok, so now we can use the numbers table for example, to act as a row generator to build our calendar table. Let’s say I want to generate all dates between 2000-01-01 and 2019-12-31. That would be 7305 days. Easy, just select that many rows from the numbers table, and add the int column N to a date. This will create a list of increasing dates.
insert
into calendar(datum)
select date_add('1999-12-31', interval n day)
from numbers
where n <=7305;
Done. You could probably see how you could have solved your problem just by using the numbers table?
Finally, here is an example of how to solve your specific problem by using the calendar table. It would of course be even easier if you added columns for Year and Month. Here I’m interpreting your question to mean “Generate one identical row for each day of each month, for the rest of the year”.
insert
into bs_events(
eventDate, eventTime, title
,spaces, description, entryFee
,payment_required, max_qty, allow_multiple
)
select datum, '10:00', 'Carpet Cleaner 3000'
,'4', 'This is rad', '25.99'
,'1', '1', '2'
from calendar
where datum >= date '2011-03-01'
and datum <= date '2011-12-31';