Retrieve data from multiple source tables with different structures

[Anonymous]

Hi,

I want to collect data from several tables in one tmp table to retrieve changes made to an ID.

The source tables have different structures, so for collect data I use UNION in tMSSqlInput :

SELECT DISTINCT 
ID,
FirstName,
LastName,
DateName,
Email,
DateEmail
PhoneNumber,
DatePhone,
OperationDate
FROM 
(
SELECT DISTINCT 
ID,
FirstName,
LastName,
DateName,
'' AS Email,
'' AS DateEmail,
'' AS PhoneNumber,
'' AS DatePhone,
DateName AS OperationDate
FROM Table1

UNION

SELECT DISTINCT 
ID,
'' AS FirstName,
'' AS LastName,
'' AS DateName,
Email,
DateEmail,
'' AS PhoneNumber,
'' AS DatePhone,
DateEmail AS OperationDate 
FROM table2

UNION

SELECT DISTINCT 
ID,
'' AS FirstName,
'' AS LastName,
'' AS DateName,
'' AS Email,
'' AS DateEmail,
PhoneNumber,
DatePhone,
DatePhone AS OperationDate 
FROM table2 
) as t

ORDER BY ID, OperationDate desc

the result is like that :

I make updates to fill the blank fields: using the tmp table in the first time, then from another table which contains the latest data for each id :

UPDATE tmp
SET tmp.LastName = tmp2.LastName
FROM #tmp tmp
INNER JOIN #tmp tmp2 ON tmp2.Id = tmp.Id AND tmp.OperationDate >= tmp2.OperationDate
WHERE (tmp.LastName is null or tmp.LastName = '')
AND (tmp2.LastName is not null and tmp2.LastName <> '')

The final result must be like this :

Table which contains the latest data for each id (old values for ID 1) :

 

This table stores the last information for each id after each execution of the job (another job).

Is there another easier way to change empty fields using Talend component or SQL Scrip ?

Thank you in advance for your replies,

[Anonymous]

I think you can simply achieve  by using outer join and distinct clause .why do u need union.

In Talend,  you can do this by using tELT components . 

[Anonymous]

hi,

The Outer join doesn't work because I want get all data : all lines with all field filled in with the value of the last line. And I'm not sure that all my source tables contain a line for an ID.

I'm looking for and testing the tELT components.

Thanks you,

FELA

[Anonymous]

Hello FELA,

Are the tELT* components Ok with you? Is your issue fixed now? Feek free to post your issue here.

Best regards

Sabrina