Split a input row to multiple row on loading SQL table

Six Stars

Split a input row to multiple row on loading SQL table

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

 


Regards,
Mohit

Accepted Solutions
Community Manager

Re: Split a input row to multiple row on loading SQL table

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


All Replies
Community Manager

Re: Split a input row to multiple row on loading SQL table

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

Six Stars

Re: Split a input row to multiple row on loading SQL table

Thanks  @rhall_2_0,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.

 


Regards,
Mohit

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads