Mysql Insert Multiple Rows with comma separated values with sql statement alone

Question

The intention to execute multiple sql insert statements without running them into some kind of loop in node.js etc.

A typical sql statement looks like this.

INSERT INTO session_speaker(session_id, speaker_id) VALUES(?, ?);
INSERT INTO session_speaker(session_id, speaker_id) VALUES(?, ?);

OR

INSERT INTO session_speaker(session_id, speaker_id) VALUES(?, ?), (?, ?);

The ordinary node.js loop looks like this.

for (var i = 0; i < request.body.length; i++) {
    queryRequest.sql += "INSERT INTO session_speaker(session_id, speaker_id) VALUES(?, ?);";
    queryRequest.values.push(request.body[i].id, request.params.id);
}

Intention/Question:

Exploring a way if possible to pass request.body to an intelligent SQL that takes array itself or maybe comma separated list of values and insert multiple rows from there without node.js loop.

request.body looks like this (changeable to meet the requirements)

[
  {
    "id": 12
  },
  {
    "id": 34
  }
]

You realize of course that all you would be doing is transferring the loop to the database, if you actually figured out a way to do it. — Dan Bracuk, Oct 07 '15 at 22:44
yup something like that without going into stored procedures, any sql function that works with arrays or comma separated values to do multiple insert, that's what the quest is about... — user2727195, Oct 07 '15 at 22:45
is this promising? http://www.sqlservercentral.com/Forums/Topic760680-338-1.aspx — user2727195, Oct 07 '15 at 22:49
Did you mean something like `insert into table values(jsonObject)`? — Hotdin Gurning, Oct 07 '15 at 22:52
not storing json object directly but basically sql to insert multiple records based on a pattern, basically sql is handling the array or comma separated values as @DanBracuk suggested. — user2727195, Oct 07 '15 at 22:54
Then you need to convert comma separated values into rows first and then do insert statement. — Hotdin Gurning, Oct 07 '15 at 23:06
Can sql handle arrays or comma separated values natively with insert statements? — user2727195, Oct 07 '15 at 23:14

score 0 · Answer 1 · edited May 23 '17 at 11:45

Yes, This is possible. Take a look at this reference. https://dev.mysql.com/doc/refman/5.7/en/json.html

Here is what you can do.

Select your object data
Convert to array
Unnest the array ( make array in column view ) reference: UNNEST function in MYSQL like POSTGRESQL, This function needs a string so convert your data (array ) into data (string) here is reference: Is there a MySQL equivalent to PostgreSQL array_to_string
Use insert into select reference: https://dev.mysql.com/doc/refman/5.1/en/insert-select.html

At the end, your code something like this:

insert  into session_speaker(session_id, speaker_id)  
select unnest_mysql ( arrayToString (dataTOArray( stringtojson (json_string) ) ) )

json_string = this is your data from php

dataTOArray = this function convert your data to mysql json to array

dataTOArray = this is to convert array to string ex. [1,2,3] to '1,2,3' unnest_mysql = your string to row data

 string '1,2,3'
 --view like 

 |-------String-----|
 |---------1--------|
 |---------2--------|
 |---------3--------|
 |----------- ------|
 So when you insert this , it will become row in your table

Note: Change my function to MySQL specific function.

I've researched a lot but is there a way you can please also provide an sql statement that works in the above case for the mysql database? I'm using node.js and request.body contains the parsed json object. — user2727195, Feb 14 '16 at 06:11

score 0 · Accepted Answer · answered Feb 18 '16 at 15:15

I think this brings me closer to what I'm looking for.

https://github.com/felixge/node-mysql/issues/814

As per dougwilson comment there and his solution for nested arrays, I can simply pass the multi object array to the query and it will insert multiple records at once.

var currentLogs = [
  [ 'Server', 'Socketio online', 'Port 3333', '2014-05-14 14:41:11' ],
  [ 'Server', 'Waiting for Pi to connect...', 'Port: 8082', '2014-05-14 14:41:11' ]
];

pool.query('INSERT INTO logs_debug (socket_id,message,data,logged) VALUES ?', [currentLogs])

Mysql Insert Multiple Rows with comma separated values with sql statement alone

2 Answers2

Linked