Angular noob Angular noob - 14 days ago 6
Javascript Question

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

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

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