循环带有日期的 SQL 插入语句 [英] looping a SQL insert statement with dates

问题描述

我需要运行一个语句，该语句每次都会使用相同的信息填充数据库，除了日期.

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.

我基本上希望日期从 01-30 增加 1，然后我将手动更改月份并在一年中的所有 12 个月内再次运行脚本.

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.

推荐答案

考虑创建一个 日历表.

我在使用的任何数据库中创建的第一个表是一个数字表，从 1 到 1000000.这样的表有很多用途，比如在 SQL 中实现循环.此外，它还可用于生成我在我使用的任何数据库上创建的第二个表:日历表.

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.

我将在下面给出一个简单但有效的 MySQL 示例:

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

数字表只是一个工作表，一旦创建了实数表，它就会被删除.数字表只有一列，它是主键.接下来，从 1 开始生成 100 万个连续整数.(听起来很慢，但实际上在我 2 岁的笔记本电脑上不到 10 秒就完成了).

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;

接下来，我将创建一个日历表.显然，它目前没有任何用处，因为它没有任何有用的列.有用列的示例包括年、月名、周数、isFirstMonthDay、isLastMonthDay、Financial Year、isHoliday、Holidayname、dayname、季度、tertial.对于非标准时期，这是黄金.

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

好的，现在我们可以使用数字表作为一个行生成器来构建我们的日历表.假设我想生成 2000-01-01 和 2019-12-31 之间的所有日期.那将是 7305 天.很简单，只需从数字表中选择那么多行，然后将 int 列 N 添加到日期.这将创建一个增加日期的列表.

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

这篇关于循环带有日期的 SQL 插入语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！