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?

Answer 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