cancel
Showing results for 
Search instead for 
Did you mean: 

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

chandrababudandi
Explorer
0 Kudos
2,939

Hi,

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.

Many Thanks in advance!!

Regards

Chandrababu Dandi

Accepted Solutions (1)

Accepted Solutions (1)

pfefferf
Active Contributor

You can use the SERIES_GENERATE_DATE function to create the required records. I'm not really sure for what that data needs to be persisted, because it can be determined one the fly, but you will have your reasons.

Following statement produces the result. It can be easily combined with an insert statement (insert into "schema"."table" select ...):

select to_nvarchar(gen_date, 'YYYY-MM-DD') as fiscal_date,
       to_nvarchar(gen_date, 'YYYY-MM-DD') as fiscal_dt,
       'EMEA' as region_cd,
       'EUR' as fiscal_region_cd,
       to_nvarchar(gen_date, 'MM/DD/YYYY') as fiscal_date1,
       'EURO' as new_region_cd
from (
  select generated_period_start as gen_date from SERIES_GENERATE_DATE('INTERVAL 1 DAY', '2018-01-01', '2018-12-31')
)

Regards,
Florian

Answers (0)