如何用SQL语句在HANA中插入连续日期？

Question

我必须像下面这样插入从2018年1月1日到2018年12月31日的数据。

insert into "schema"."tablename" (FISCAL_DATE,FISCAL_DT,REGION_CD,FISCAL_REGION_CD,FISCAL_DATE1,NEW_REGION_CD) values ('2018-01-01', '2018-01-01','EMEA','EUR','01/01/2018', 'EURO'); 
insert into "schema"."tablename" (FISCAL_DATE,FISCAL_DT,REGION_CD,FISCAL_REGION_CD,FISCAL_DATE1,NEW_REGION_CD) values ('2018-01-01', '2018-01-01','EMEA','EUR','01/01/2018', 'EURO'); 
insert into "schema"."tablename" (FISCAL_DATE,FISCAL_DT,REGION_CD,FISCAL_REGION_CD,FISCAL_DATE1,NEW_REGION_CD) values ('2018-01-01', '2018-01-01','EMEA','EUR','01/01/2018', 'EURO'); 
insert into "schema"."tablename" (FISCAL_DATE,FISCAL_DT,REGION_CD,FISCAL_REGION_CD,FISCAL_DATE1,NEW_REGION_CD) values ('2018-01-01', '2018-01-01','EMEA','EUR','01/01/2018', 'EURO');
...
insert into "schema"."tablename" (FISCAL_DATE,FISCAL_DT,REGION_CD,FISCAL_REGION_CD,FISCAL_DATE1,NEW_REGION_CD) values ('2018-12-31', '2018-12-31','EMEA','EUR','12/31/2018', 'EURO');

是否有其他SQL语句可以实现这一点，或者需要手动逐个更新日期。

请分享您的意见/建议。

Answer 1

如果我没理解错的话，您希望插入日期从2018-01-01到2018-12-31的多个记录(逐日)。可以使用WHILE语句来实现这一点。

DO 
BEGIN

    DECLARE INPUT_DATE date := '2018-01-01';


    WHILE INPUT_DATE <= '2018-12-31'  DO

        INSERT INTO "schema"."tablename" (FISCAL_DATE,FISCAL_DT,REGION_CD,FISCAL_REGION_CD,FISCAL_DATE1,NEW_REGION_CD) values (:INPUT_DATE, :INPUT_DATE,'EMEA','EUR',:INPUT_DATE, 'EURO');

        INPUT_DATE = ADD_DAYS(:INPUT_DATE,1);

    END WHILE;

END

Answer 2

您也可以使用SQLScript创建SQL dates table on HANA database，这非常容易。

请检查以下示例(请根据您的机型替换表名和列名)

do
begin

declare date_start date := '01.01.2018';
declare date_end date := '31.12.2018';

insert into mySampleTransactionData (tdate)
SELECT generated_period_start
 FROM SERIES_GENERATE_DATE('INTERVAL 1 DAY', :date_start, add_days(:date_end,1));

end

Answer 3

我已经用下面的查询成功地插入了数据。

insert into "Schema"."tablename" (select to_nvarchar(gen_date, 'YYYY-MM-DD') as fiscal_date,
       to_nvarchar(gen_date, 'YYYY-MM-DD') as fiscal_dt,
       'AMR' as region_cd,
       'AMR' as fiscal_region_cd,
       to_nvarchar(gen_date, 'MM/DD/YYYY') as fiscal_date1,
       'AMR' as led_region_cd
from (
  select generated_period_start as gen_date from SERIES_GENERATE_DATE ('INTERVAL 1 DAY', '2018-01-01', '2018-12-31')
));

感谢大家的宝贵建议！！