Just chewing the fat over multi record inserts

Coffee Lounge
#1

I am just thinking about recent comments regarding the multi record insert action performance.

Looks like each insert is performed singly within a repeat which seems a pretty inefficient way to do this.

Now, taking the sql logic from inserting data from queries into tabies “en-bloc” using syntax such as:

INSERT INTO table SELECT {fields).... WHERE {condition)

I started to think.

In most cases we would be drawing data from a JSON object which, traditionally, we would use the basis of a repeat and multiple actions based on that.

Could there be a more efficient method by, for example, parsing the json to an array and then inserting directly into the recipient table using a similar syntax to that above?

This would appear, on the face of it, far more efficient?

Just bouncing ideas, sure there are lots of different ways to do this

4 Likes
#2

Something like?

INSERT INTO Info (id,Cost,city)  
VALUES 
(1,200,'Sydney'), 
(2,150,'New York'), 
(3,345,'Paris');

This is where a single INSERT query inserts multiple records within a single point of execution.

1 Like
#3

Exactly ben, single insert from source derived from another data source.

2 Likes
#4

Btw Knex (from NodeJS server target) already supports that natively (inserting rows from array of objects)

1 Like
#5

Guess that should make it easier for the team to do this then.
I really think this should ge a tweak to the wappler server connect core rather than via an extension.
What do you think @George ?

1 Like
#6
#7

I think @Patrick was already working on a bulk insert action

6 Likes
#8

One can always do this by building a string, let’s call it sql_stmt, that contains exact what Ben typed, and then running a Custom Query. You would put in the boy of the custom query just this

{sql_stmt}

I’ve done this before and it worked pretty well.

Hope this helps,

Alex

1 Like
#9

It’s a good workaround for small data sets but whatever really need is to replicate the syntax of insert from query such as:

INSERT INTO table2
SELECT FIELD1,field  FROM table1
WHERE condition;

To allow insertion of a query into a table

#10

Bulk insert is now available in server connect:

3 Likes
#11