Split a input row to multiple row on loading SQL table

[mks02]

I have a SQL table having few records for different data range as below:

CREATE TABLE tmp.[EMP_REC](
[EMPID] [int] NOT NULL,
[DEPTID] [int] NOT NULL,
[STARTDTM] [datetime] NOT NULL,
[ENDDTM] [datetime] NOT NULL,
[EMPTYPE] [INT] NOT NULL
)

-- INSERT statement
INSERT [TMP].[EMP_REC] ([EMPID], [DEPTID], [STARTDTM], [ENDDTM], [EMPTYPE]) VALUES (1282539, 28993, CAST(N'2017-08-06 00:00:00.000' AS DateTime), CAST(N'2017-08-12 00:00:00.000' AS DateTime),0)
INSERT [TMP].[EMP_REC] ([EMPID], [DEPTID], [STARTDTM], [ENDDTM], [EMPTYPE]) VALUES (1282540, 28994, CAST(N'2017-08-06 00:00:00.000' AS DateTime), CAST(N'2017-08-12 00:00:00.000' AS DateTime),0)
INSERT [TMP].[EMP_REC] ([EMPID], [DEPTID], [STARTDTM], [ENDDTM], [EMPTYPE]) VALUES (1282541, 29001, CAST(N'2017-08-06 00:00:00.000' AS DateTime), CAST(N'2017-08-12 00:00:00.000' AS DateTime),0)
INSERT [TMP].[EMP_REC] ([EMPID], [DEPTID], [STARTDTM], [ENDDTM], [EMPTYPE]) VALUES (1282542, 29001, CAST(N'2017-08-06 00:00:00.000' AS DateTime), CAST(N'2017-08-12 00:00:00.000' AS DateTime),0)
INSERT [TMP].[EMP_REC] ([EMPID], [DEPTID], [STARTDTM], [ENDDTM], [EMPTYPE]) VALUES (1282543, 28997, CAST(N'2017-08-01 00:00:00.000' AS DateTime), CAST(N'2017-08-05 00:00:00.000' AS DateTime),0)
INSERT [TMP].[EMP_REC] ([EMPID], [DEPTID], [STARTDTM], [ENDDTM], [EMPTYPE]) VALUES (1282544, 28998, CAST(N'2017-08-01 00:00:00.000' AS DateTime), CAST(N'2017-08-05 00:00:00.000' AS DateTime),0)
INSERT [TMP].[EMP_REC] ([EMPID], [DEPTID], [STARTDTM], [ENDDTM], [EMPTYPE]) VALUES (1282545, 28999, CAST(N'2017-08-01 00:00:00.000' AS DateTime), CAST(N'2017-08-05 00:00:00.000' AS DateTime),0)
INSERT [TMP].[EMP_REC] ([EMPID], [DEPTID], [STARTDTM], [ENDDTM], [EMPTYPE]) VALUES (1282546, 28997, CAST(N'2017-08-20 00:00:00.000' AS DateTime), CAST(N'2017-08-22 00:00:00.000' AS DateTime),0)
INSERT [TMP].[EMP_REC] ([EMPID], [DEPTID], [STARTDTM], [ENDDTM], [EMPTYPE]) VALUES (1282547, 29001, CAST(N'2017-08-20 00:00:00.000' AS DateTime), CAST(N'2017-08-22 00:00:00.000' AS DateTime),0)

Prob: I need to insert these distinct values to new TEMP table on the basis of date range (strtdtm and enddtm).
Eg. For row 1 start date is 6Sept and end date is 12sept, so on inserting into new table, it should create new row for every date.
Now row 1 is divided in 6 rows on basis of dates:
6Sept --> row1
7Sept --> row2
8Sept --> row3
....
....
12Sept --> row6

all the values same for that row.

TGT table structure below:

CREATE TABLE tmp.[EMP_REC_DATES](
[EMPID] [int] NOT NULL,
[DEPTID] [int] NOT NULL,
[FLOAT_DT] [datetime] NOT NULL,
[EMPTYPE] [INT] NOT NULL
)

Please let me know how do I achieve this in Talend Job. I am using 5.2 version

[Anonymous · Accepted solution]

This tutorial might help (https://www.rilhia.com/quicktips/quick-tip-row-multiplication). It doesn't do exactly this, but it shows you how to achieve this. The big difference between your requirement and this tutorial is the number of records created and how. You will need to drive your by dates. This is something that you will need to figure out, but it isn't too hard. Look at Java date difference code. Give it a go and if you get stuck, give us a shout

[mks02]

Thanks  @rhall,it works for me...

One more approach I implemented that :

1) Concatenate the dates through SQL in a separate column with delimiter ',' Eg. new column combineDates (2017-08-06,2017-08-07,2017-08-08,2017-08-09,2017-08-10)

2) After that process those records in tNormalize (using combineDates column for normalize and separator ',' ), for returning all rows having different dates for that combineDates column.

3) then store it to new table/file.