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. 

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

[Vinodh247]

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