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
– Dan Bracuk, Commented Oct 7, 2015 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
– user2727195, Commented Oct 7, 2015 at 22:45
is this promising? sqlservercentral.com/Forums/Topic760680-338-1.aspx — user2727195
– user2727195, Commented Oct 7, 2015 at 22:49
Did you mean something like insert into table values(jsonObject)? — Hotdin Gurning
– Hotdin Gurning, Commented Oct 7, 2015 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
– user2727195, Commented Oct 7, 2015 at 22:54

Community · Accepted Answer · 2017-05-23 11:45:01Z

0

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.

edited May 23, 2017 at 11:45

CommunityBot

11 silver badge

answered Oct 7, 2015 at 23:36

ji-ruh

7011 gold badge7 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2727195 Over a year ago

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 · Accepted Answer · 2016-02-18 15:15:37Z

0

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])

answered Feb 18, 2016 at 15:15

user2727195

7,35020 gold badges74 silver badges121 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related