Bulk insert using transformer output

Hello everyone,
I am struggling to make an insert query for MySQL which contains transformer output.

What I want do make eventually is something like this:

(db, tbl, op_name) VALUES
('dbname1', 'tblname1', 'manager_name1'), 
('dbname2', 'tblname2', 'manager_name2')

And I want the list of columns in the insert query to be dynamic, so I'm trying this:

{{ transformer3_header.value[0] }} VALUES
{{ transformer4_alldata.value }}

from transformer3_header, I got result like this:
{{ transformer3_header.value }} is Array
{{ transformer3_header.value[0] }} is String

0: "(db, tbl, op_name)"

from transformer4_alldata, I got result like this:
{{ transformer4_alldata.value }} is String.

"('dbname1', 'tblname1', 'manager_name1'),('dbname2', 'tblname2', 'manager_name2')"

But when I run the query using transformers, I got syntax error like below:

ER_PARSE_ERROR: You have en error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''(db,tbl,op_name)' VALUES '(\'dbname1\',\'tblname1\',\'manager_na' at line 2

I tried .toString() also, It gives the same error.
It seems the results of transformers are not like the one I see on transformer tab.

So my question is...
how can I remove redundant single quotes and backslashes from transformers values?

Thank you in advance.

You want to use a bulk insert query for this scenario. A SQL Mode (aka manual mode) update or insert query can only operate on a single record.

Here is a good start form the docs. You can also find many tips and tricks for doing bulk update queries in the forum.


If you care to know the reason you must do it this way, do a search for parameterized query on the forum to learn how retool passed the query to the database.

I didn't realize that I can produce array of records from transformers. I made it in GUI mode using bulk insert feature as you mentioned.
Thank you for your help!

Linking another thread where we bulk inserted with SQL mode so that we can return the newly created records