topic Re: Running a MERGE (SQL) in TALEND in Talend Studio

Running a MERGE (SQL) in TALEND

stephbzr — Fri, 15 Nov 2024 22:37:40 GMT

Hello,

Here is a SQL query with MERGE that allows me to UPDATE several rows at once in my database (with a join).

I need to translate this idea into TALEND, but I have no idea how I'm going to do it. The data from ID_TRTM and the data after each "THEN", my TALEND job has them (these are statistics about a file such as number of rows, size, etc..).

Do you have any ideas ? Thank you.

Re: Running a MERGE (SQL) in TALEND

Jmiles1658991691 — Thu, 25 Aug 2022 11:19:27 GMT

Hi @Stéphane Barbezier you can run this query in talend with tDBRow component

Re: Running a MERGE (SQL) in TALEND

stephbzr — Sun, 28 Aug 2022 12:37:00 GMT

Thank you very much @Juanita miles , I learned what this component does and it works very well for what I am looking for.

Re: Running a MERGE (SQL) in TALEND

ThWabi — Wed, 07 Sep 2022 17:32:27 GMT

Hello stephbzr,

as I understand the SQL merge statement, the select after "using" (i.e. the table TMP) is your input data (for a tMap component). The table TMPD is the lookup table and also the output/target.

The "on" condition (TMPD.ID_TRT_MUTIX_PARAM = TMP.ID_TRT_MUTIX_PARAM) is the join between the input and lookup table. As your merge statement only has an "on matched" clause, the join model in the tMap is set to "inner join". (We can discard non-matching rows.)

Please find the uploaded screenshot for a detailed outline.

The "Update_Flag" is the expression

TMP.TRT_MUTIX_PARAM_CODE.equals("NB_LIGNES_FIC") || TMP.TRT_MUTIX_PARAM_CODE.equals("TAILLE_FIC") || TMP.TRT_MUTIX_PARAM_CODE.equals("ID_HIST")

and "Update_Value" is

TMP.TRT_MUTIX_PARAM_CODE.equals("NB_LIGNES_FIC") ? 1000 : TMP.TRT_MUTIX_PARAM_CODE.equals("TAILLE_FIC") ? 2000 : TMP.TRT_MUTIX_PARAM_CODE.equals("ID_HIST") ? 3000 : 0

An update to the zero value can not occur because of the use of the Update_Flag as a filter on the output side.

The tMap output then connects to your DBOutput component using "update" as the "action on data" setting.

Best regards,

Thomas