How can I create a script which takes data from a table as a parameter and uses this for a looped merge script in SQL

[SiljaZiegler]

• Goal: I have a list of data (TE). When someone clicks on a button, then a merge script (export to SQL) with a loop should run, so for each TE the merge script should run and inserts/updates data in a table. The data to merge is in a text field.

• Steps: I created one script to select the data that can be used as parameter, another script where the merge query is saved,

• Details:

1. SQL Script to get the list of parameters (getParameters_SRL):
   SELECT DISTINCT TE
   FROM [sv_business_db_test].[svo].[abrechnung_srl_mit_faktor_new]
   WHERE
   ({{ !year_srl.value ? 1 : 0 }} = 1 OR YEAR(datum) = {{ year_srl.value }})
   AND ({{ !month2.value ? 1 : 0 }} = 1 OR MONTH(datum) = {{ month2.value }})
   AND ({{ !select24.selectedItem ? 1 : 0 }} = 1 OR TE = {{ select24.selectedItem.te }})

2. Script for merging data (mergeQuery):
   MERGE INTO svo.srl_sum_arbeits_abrechnung AS target
   USING (VALUES
   {{year_srl}}, {{month2}}, '{{TE}}', '{{anlagen_name}}',
   {{sum_HK_abruf_SRL}}, {{sum_netto_SRL}}, {{sum_erlos_srl}}, {{sum_akzeptierte_poolpartner_srl}},
   {{Verguetung}}, {{FahrplanKosten_new}}, {{FahrplanFaktor}}, {{Abrufstage_MRLSRL}},
   {{sum_HK_abruf_SRL}}, {{sum_netto_SRL_2}}, {{sum_erlos_srl_2}}, {{sum_akzeptierte_poolpartner_srl}}, {{Verguetung_2}}
   ) AS source (jahr, monat, te, anlagen_name, sum_hk_abruf, sum_netto, sum_erlos_UENB,
   sum_akz_leistung_pool, verguetung, fahrplankosten, fahrplanmanagement, abruftage_auchMRL, sum_hk_abruf2, sum_netto2,
   sum_erlos_UENB2, sum_akz_leistung_pool2, verguetung2)
   ON target.te = source.te AND target.jahr = source.jahr AND target.monat = source.monat
   WHEN MATCHED THEN
   UPDATE SET
   jahr = source.jahr,
   monat = source.monat,
   te = source.te,
   anlagen_name = source.anlagen_name,
   sum_hk_abruf = source.sum_hk_abruf,
   sum_netto = source.sum_netto,
   sum_erlos_UENB = source.sum_erlos_UENB,
   sum_akz_leistung_pool = source.sum_akz_leistung_pool,
   verguetung = source.verguetung,
   fahrplankosten = source.fahrplankosten,
   fahrplanmanagement = source.fahrplanmanagement,
   abruftage_auchMRL = source.abruftage_auchMRL,
   sum_hk_abruf2 = source.sum_hk_abruf2,
   sum_netto2 = source.sum_netto2,
   sum_erlos_UENB2 = source.sum_erlos_UENB2,
   sum_akz_leistung_pool2 = source.sum_akz_leistung_pool2,
   verguetung2 = source.verguetung2
   WHEN NOT MATCHED THEN
   INSERT (jahr, monat, te, anlagen_name, sum_hk_abruf, sum_netto, sum_erlos_UENB,
   sum_akz_leistung_pool, verguetung, fahrplankosten, fahrplanmanagement, abruftage_auchMRL, sum_hk_abruf2, sum_netto2,
   sum_erlos_UENB2, sum_akz_leistung_pool2, verguetung2)
   VALUES (source.jahr, source.monat, source.te, source.anlagen_name, source.sum_hk_abruf, source.sum_netto, source.sum_erlos_UENB,
   source.sum_akz_leistung_pool, source.verguetung, source.fahrplankosten, source.fahrplanmanagement, source.abruftage_auchMRL, source.sum_hk_abruf2, source.sum_netto2,
   source.sum_erlos_UENB2, source.sum_akz_leistung_pool2, source.verguetung2);

3. Script to combine everything and create a loop (getParameters2):
   const parameters = getParameters_SRL.data; // Deine Parameter-Daten aus der API

// Sicherstellen, dass parameters ein Array ist
const paramArray = Array.isArray(parameters) ? parameters : [parameters];

// Durch die Parameter iterieren
paramArray.forEach(param => {
// Triggern der Merge-Query mit den aktuellen Parametern
mergeQuery.trigger({
additionalScope: {
Jahr: year_srl.value,
Monat: month2.value,
TE: param.TE,
sum_HK_abruf_SRL: sum_HK_abruf_SRL.value,
sum_netto_SRL: sum_netto_SRL.value,
sum_erlos_srl: sum_erlos_srl.value,
sum_akzeptierte_poolpartner_srl: sum_akzeptierte_poolpartner_srl.value,
Verguetung: Verguetung.value,
FahrplanKosten_new: FahrplanKosten_new.value,
FahrplanFaktor: FahrplanFaktor.value,
Abrufstage_MRLSRL: Abrufstage_MRLSRL.value,
sum_netto_SRL_2: sum_netto_SRL_2.value,
sum_erlos_srl_2: sum_erlos_srl_2.value,
sum_akzeptierte_poolpartner_srl: sum_akzeptierte_poolpartner_srl.value,
Verguetung_2: Verguetung_2.value
}
});
});

Error message:
mergeQuery failed (0.689s):Incorrect syntax near '@param1'.

from mergeQuery response(mergeQuery)

in mergeQuery.trigger()(mergeQuery)

1. year_srl: {pluginType: "SelectWidget2", value: 2024, _imageByIndex: Array(2), itemMode: "static", imageByIndex: Array(2)…}

2. month2: {pluginType: "SelectWidget2", value: 6, _imageByIndex: Array(12), itemMode: "static", imageByIndex: Array(12)…}

3. TE: (1) ["1-Test"]

4. anlagen_name: (1) ["Test"]

sum_HK_abruf_SRL: 0sum_netto_SRL: 0sum_erlos_srl: 0sum_akzeptierte_poolpartner_srl: 0

1. Verguetung: (1) [0]

FahrplanKosten_new: "0"

1. FahrplanFaktor: (1) [0]

Abrufstage_MRLSRL: 0sum_netto_SRL_2: 0sum_erlos_srl_2: 0

1. Verguetung_2: (1) [0]

2. additionalScope: {Jahr: 2024, Monat: 6, TE: Array(1), anlagen_name: Array(1), sum_HK_abruf_SRL: 0…}
   environment: "production"
   from preview

The data seems to be ok, but the data is not saved in SQL Table

• Screenshots: the data I want to export to SQL is coming from calculated textfields:
  
  

[Tess]

Hi @SiljaZiegler,

Thanks for reaching out

It's a little hard to tell in this format, but it seems like year_srl, month2, and, anlagen_name still need to be defined within the additionalScope of getParameters2.

Apologies if I'm misinterpreting! Screenshots or an app export could be helpful