How to INSERT the continuous Date in HANA with SQL statement?

Question

I have to insert the data like as below from 01-01-2018 to 31-12-2018.

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

Is there any alternative SQL statements to achieve this or else need to go with manually update the dates one by one.

Please share your inputs/suggestions.

score 0 · Answer 1 · answered Oct 03 '18 at 14:14

If I understood you correctly, you want to insert multiple records (day by day) with dates from 2018-01-01 to 2018-12-31. You can achieve this using WHILE statement.

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

score 0 · Answer 2 · answered Oct 04 '18 at 05:42

You can also use a SQL dates table on HANA database and it is very easy to create using SQLScript.

Please check following example (please replace table name and column names according to your model)

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

score 0 · Answer 3 · edited Oct 04 '18 at 14:26

I have successfully inserted data with the below Query.

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

Thank You all for your valuable suggestions!!

How to INSERT the continuous Date in HANA with SQL statement?

3 Answers3