looping a SQL insert statement with dates

Question

I need to run a statement that will populate a data base with the same information every time EXCEPT for the date.

im currently trying something along these lines and aren't having any luck.

DECLARE 
i_int BINARY_INTEGER := 1;
  BEGIN
    WHILE i_int <= 30 LOOP
      INSERT INTO bs_events (eventDate,eventTime,title,spaces,description,entryFee,payment_required,max_qty,allow_multiple) VALUES ('2011-03-i_int 00:00:00', '10:00','Carpet Cleaner 3000','4','This is rad','25.99','1','1','2');
      i_int := i_int + 1;
    END LOOP;
  END;
  /

I can't seem to get it to work and im stuck in a code hole and cannot think straight.

I basically want the date to up by one from 01-30 and then i will manually change the month and run the script again for all 12 months of the year.

Answer 1

You could try DATEADD(ms,i_int,GETDATE()) for your date.

Answer 2

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';

Answer 3

INSERT INTO bs_events (eventDate, ... ) 
VALUES ('2011-03-i_int 00:00:00', ...);

This value, 2011-03-i_int 00:00:00, is a string, not a timestamp. You were hoping your SQL environment would substitute the value of i_int for its symbol. It won't do that.

Instead, do the date arithmetic outside the INSERT statement.

In PostgreSQL (PL/pgsql) you could do this to add a day to a variable of type 'timestamp'.

this_date = this_date + interval '1 day';

And in the INSERT statement,

INSERT INTO bs_events (eventDate, ... ) 
VALUES (this_date, ...);

Check your documentation to see what the syntax might be for your platform.

Answer 4

DECLARE 
i_int BINARY_INTEGER := 0;
  BEGIN
    WHILE i_int < 30 LOOP
      INSERT INTO bs_events (
        eventDate, eventTime, title,
        spaces, description, entryFee,
        payment_required, max_qty, allow_multiple)
      VALUES (
        DATEADD(day, i_int, '2011-03-01 00:00:00'), '10:00', 'Carpet Cleaner 3000',
        '4', 'This is rad', '25.99',
        '1', '1', '2');
      i_int := i_int + 1;
    END LOOP;
  END;
  /