Is Bulk Insert Query supported in Execute SQL (MySQL) node?

[dcpartners]

Describe the problem/error/question

We are trying to run a bulk insert into Execute SQL Node. We ran a formatting issue [undefined]. We even tested the workflow and it’s same error before even we run it.

What is the error message (if any)?

INSERT INTO dc_ai_content_moderation (Batch_ID, Listing_ID)
VALUES [undefined];

Please share your workflow

Alternatively, we could run individual insert BUT it’s not efficient. BUt if it’s not support it, we have to go down this path.

[hubschrauber]

It looks like you are very close but it is difficult (maybe not possible) to blend values from expressions within execution of the JS map. One alternative is to augment the objects in the array such that they each contain all the fields you need and then map() to get your SQL VALUES. Here is an example of that which might help you adjust your workflow.

If this post answers your question, please mark it as the Solution

[dcpartners]

Thanks for this.

We’re a quite newbie for this N8N

So we are assuming the Build SQL node with expression is a SET node. Right?

Also, I am assuming to intgrate into out SQL bulk insert (Execute Query Node), I just drag the value into this query:

INSERT INTO dc_ai_content_moderation (Batch_ID, Listing_ID)
VALUES {{ allitemsAsSqlValuesArray }} … is that right?

Appreciated your input.

Thanks

[dcpartners]

Hi @hubschrauber

Never mind we worked it out

We tested with a small sample … 3 in our case … followed your workflow and it generated the sql statement.

We noticed though when we tested with a real live data which daily it has around 500-600 items and the N8N mentionen that it’s too big to display which is around 300-400 MB files. I guess an array should handle this pretty well? Is that right?

[hubschrauber]

From the screen shot, it was hard to tell just how big the Content was in your data, but I figured that would be your next challenge. I think the next part you will need is the Loop Over Items node, and you can set the batch size to 50 or whatever gets your SQL down to a manageable size per insert / SQL execution.

I hope you also figured out that the second Set/Edit node in the example is just to show the expression working without involving a DB call. You could also put that expression directly in your SQL block. The first Set/Edit node is the key to getting all the data for each inserted row together in each item.

Don’t forget to mark the answer above as the Solution. That helps others who find this later know that there was an answer and which response has the helpful bits.