Bulk insert using transformer output

Queries and Resources
,
1

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:

INSERT INTO mytable
(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:

INSERT INTO mytable
{{ 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.
padam

2

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.

https://docs.retool.com/docs/adding-a-new-row-to-your-database-or-api#creating-a-bulk-update-query

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.

3

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!

5

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