topic Re: Dynamical generate values with Script Activity with SQL Insert in Pipelines

Dynamical generate values with Script Activity with SQL Insert

P_work — Fri, 12 Sep 2025 19:22:43 GMT

Say I have a return array from a lookup activity as follows:
[ { "mynum": 1, "myval": "A" },{ mynum": 2, "myval": "A" },{ mynum": 3, "myval": "ZZ" }]

And I want to insert these rows into mytbl in a script activity.

Is it possible to create the string VALUES statement dynamically? I thought of for-each and set variable activity, but can not reference the variable in the set variable activity.

Re: Dynamical generate values with Script Activity with SQL Insert

Vinodh247 — Sat, 13 Sep 2025 07:32:38 GMT

Build the VALUES string in one go (preferred solution to try)

Instead of looping, you can use an expression to flatten your array into a SQL VALUES string.

@concat( 'INSERT INTO mytbl (mynum, myval) VALUES ', join( select(activity('Lookup1').output.value, concat('(', item().mynum, ', ''', item().myval, ''')')), ',' ) )

This generates the following, You can then feed this single string into a Script activity as @{...}.

INSERT INTO mytbl (mynum, myval) VALUES (1,'A'),(2,'A'),(3,'ZZ')

/Or/

ForEach + Script (row-by-row)

If the dataset is small and you do not care about performance, wrap your INSERT in a ForEach.

This will execute one insert per item. It is slower but easier to reason about.

Inside ForEach, use Script activity with: INSERT INTO mytbl (mynum, myval) VALUES (@{item().mynum}, '@{item().myval}')

Re: Dynamical generate values with Script Activity with SQL Insert

v-hjannapu — Mon, 15 Sep 2025 08:46:47 GMT

Hello P_work,

I would also take a moment to thank Vinodh247 , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Community Support Team.

Re: Dynamical generate values with Script Activity with SQL Insert

v-hjannapu — Thu, 18 Sep 2025 12:16:02 GMT

Hi P_work,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.

Regards,
Community Support team.

Re: Dynamical generate values with Script Activity with SQL Insert

v-hjannapu — Mon, 22 Sep 2025 04:05:49 GMT

Hi P_work,
I wanted to follow up and see if you have had a chance to review the information that was shared. If you have any additional questions or need further clarification, please don’t hesitate to reach out. I am here to assist with any concerns you might have.

Regards,
Community Support Team.

Re: Dynamical generate values with Script Activity with SQL Insert

P_work — Wed, 22 Oct 2025 14:45:25 GMT

Did not work. Required ForEach activity with Append Variable activity. Similar to this solution:
https://community.fabric.microsoft.com/t5/Service/Formatting-Array-Variable-as-HTML-inside-Fabric-Data-Pipeline/m-p/3933628