Dynamical generate values with Script Activity with SQL Insert

[P_work]

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. 

[Vinodh247 · Accepted solution]

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}')

[v-hjannapu]

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.

[v-hjannapu]

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.

[v-hjannapu]

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.

[Vinodh247 · Accepted solution]

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}')

[P_work · Accepted solution]

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-Da...