How do I insert multiple records from array in mySQL using node.js

Question

I have a look-up table:

CREATE TABLE technologies (
    technologyName VARCHAR(50) NOT NULL PRIMARY KEY
);

I want to store a new record in this table for every element in an array:

var values = ['Grunt', 'Gulp'];

So that the resulting table will look like:

+----------------+
| technologyName |
+----------------+
| Grunt          |
+----------------+
| Gulp           |
+----------------+

How can I do this using Node?

Update:

I know I can do something like this:

var values = [
  [
    ['Grunt'], 
    ['Something']
  ]
]
connection.query('INSERT IGNORE INTO technologies (technologyName) VALUES ?', values);

I tried this with the original array but it does not work. How can I convert the simple array into the more complicated one?

@MelissaAvery-Weir No, I do not need do do that. I made the choice to keep the question short and sweet for anyone who might stumble upon it in the future. — Angular noob
– Angular noob, Commented Apr 24, 2015 at 14:48
Nope, you certainly don't need to (i.e., the question is unlikely to be closed). I should have said "your question will be more highly voted and more readily answered if you include what you've tried" (per stackoverflow.com/help/how-to-ask). That all said, thanks for adding the example. — Melissa Avery-Weir
– Melissa Avery-Weir, Commented Apr 24, 2015 at 14:58

J-C FOREST · Accepted Answer · 2016-11-24 15:19:46Z

2

if you want to use "?" to escape your values and avoid SQL injections, mysqljs accept nested arrays for bulk inserts. So the answer to your update is :

var valuesNestedArray  = [];

for(var v in values ) {
  valuesNestedArray.push([values[v]]);
}

var sql = "INSERT IGNORE INTO technologies (technologyname) values ?";
connection.query(sql, [valuesNestedArray], function(err) {
    if (err) throw err;   
});

answered Nov 24, 2016 at 15:19

J-C FOREST

3214 silver badges11 bronze badges

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

Comments

Charlie Wynn · Accepted Answer · 2015-04-24 15:35:19Z

1

You can insert multiple things at once...

From the mysql docs (https://dev.mysql.com/doc/refman/5.5/en/insert.html)

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

so for you:

insert into technologies (technologyname) values ('grunt'),('gulp')

var sql = "insert into technologies (technologyname) values ";

for(var v in values)
  sql += "('" + connection.escape(values[v]) + "'),";

sql = sql.substr(0,sql.length-1); // take off the last comma since we added one after each value

edited Apr 24, 2015 at 15:35

answered Apr 24, 2015 at 14:35

Charlie Wynn

9331 gold badge8 silver badges23 bronze badges

4 Comments

Angular noob Over a year ago

Is it possible to do this using ? e.g. INSERT IGNORE INTO technologies (technologyName) VALUES ??

Charlie Wynn Over a year ago

@Angularnoob I'm pretty sure you can't insert multiple rows from a multi-value parameter (unfortunately!)

Angular noob Over a year ago

Ah, OK. I am afraid, by the way, that your code does not work, presumably because there is no comma?

Charlie Wynn Over a year ago

@Angularnoob, yep, didn't have a comma between the values.. fixed in the comment now. I add a comma after each item, then take the last character off of the whole sql to get rid of the extra comma

Collectives™ on Stack Overflow

How do I insert multiple records from array in mySQL using node.js

2 Answers 2

Comments

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Related