Dynamical generate values with Script Activity with SQL Insert

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

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