Good morning, this is my first post on Talend forum; I have a specific question about importing data from a source to anotherone (SQL data base).

To be simple, in this case we can consider 3 .xls files (then the procedure can be used also with diferent input/output objects):

INPUT.xlsx  --> Contains the input data that can be new data, modified data (to be updated in the OUTPUT) or missing data (in this case we want to change a column value in the OUTPUT)

OUTPUT.xlsx --> Contains the output data with the Primary Key

CONTATORE.xlsx --> Contains the LAST_ID(int; not null) field: this number is the last number used in OUTPUT as incremental Primary Key.


I WANT MANAGE 4 CASES:

1. INSERT new records assigning the correct ID as PK using CONTATORE.xlsx

Why I need of the CONTATORE file: the PK (primary key) of the OUTPUT  is not auto-incremental, thus I have to handle manually the increment of that field that can't be NULL or duplicated; in this way on Insert data:
a) I check the LAST_ID in CONTATORE
b) I insert in the OUTPUT the record with ID = LAST_ID+1
c) I set the new LAST_ID

NOTE: I want to increment always the ID relatively to the LAST_ID in CONTATORE.xlsx even if in OUTPUT file I have some missing ID values (e.g. if I delete some records)

2. MODIFY some existing records if in the INPUT I have different data using a fixed field --> check if record exist and then changing the values

3. SET STATE = 'Deactivated' (in OUTPUT file) if one record was removed from INPUT file

4. DELETE record in the OUTPUT.xlsx if it does not exist in the INPUT.xlsx

------------------------------------------------
Below you can see, in order LEFT --> RIGHT: CONTATORE, INPUT and OUTPUT sample files.

Last edited by ienax_ridens (2010-11-08 19:02:17)