Asked 10 years, 7 months ago Viewed 202k times
Bulk inserts are possible by using nested array, see the github page Nested arrays are turned into grouped lists (for bulk inserts), e.g. [['a', 'b'], ['c', 'd']] turns into ('a', 'b'), ('c', 'd')
You just insert a nested array of elements. An example is given in
here var mysql = require('mysql');
var conn = mysql.createConnection({
...
});
var sql = "INSERT INTO Test (name, email, n) VALUES ?";
var values = [
['demian', '', 1],
['john', '', 2],
['mark', '', 3],
['pete', '', 4]
];
conn.query(sql, [values], function(err) {
if (err) throw err;
conn.end();
});
Note: values is an array of arrays wrapped in an array [ [ [...], [...], [...] ] ]
There is also a totally different node-msql package for bulk insertion answered Jan 10, 2013 at 13:44 Ragnar123Ragnar123 4,8744 gold badges22 silver badges34 bronze badges 19 I ran into this today (mysql 2.16.0) and thought I'd share my solution: const items = [
{name: 'alpha', description: 'describes alpha', value: 1},
...
];
db.query(
'INSERT INTO my_table (name, description, value) VALUES ?',
[items.map(item => [item.name, item.description, item.value])],
(error, results) => {...}
);
answered Aug 26, 2018 at 22:04 3 @Ragnar123 answer is correct, but I see a lot of people saying in the comments that it is not working. I had the same problem and it seems like you need to wrap your array in [] like this: var pars = [
[99, "1984-11-20", 1.1, 2.2, 200],
[98, "1984-11-20", 1.1, 2.2, 200],
[97, "1984-11-20", 1.1, 2.2, 200]
];
It needs to be passed like [pars] into the method. honk 8,39111 gold badges74 silver badges77 bronze badges answered May 16, 2017 at 5:27 CodendaalCodendaal 5075 silver badges14 bronze badges 3 I was looking around for an answer on bulk inserting Objects. The answer by Ragnar123 led me to making this function: function bulkInsert(connection, table, objectArray, callback) {
let keys = Object.keys(objectArray[0]);
let values = objectArray.map( obj => keys.map( key => obj[key]));
let sql = 'INSERT INTO ' + table + ' (' + keys.join(',') + ') VALUES ?';
connection.query(sql, [values], function (error, results, fields) {
if (error) callback(error);
callback(null, results);
});
}
bulkInsert(connection, 'my_table_of_objects', objectArray, (error, response) => {
if (error) res.send(error);
res.json(response);
});
Hope it helps!
answered Aug 3, 2017 at 22:41 All props to Ragnar123 for his answer. I just wanted to expand it after the question asked by Josh Harington to talk about inserted IDs. These will be sequential. See this answer : Does a MySQL multi-row insert
grab sequential autoincrement IDs? Hence you can just do this (notice what I did with the result.insertId): var statement = 'INSERT INTO ?? (' + sKeys.join() + ') VALUES ?';
var insertStatement = [tableName, values];
var sql = db.connection.format(statement, insertStatement);
db.connection.query(sql, function(err, result) {
if (err) {
return clb(err);
}
var rowIds = [];
for (var i = result.insertId; i < result.insertId + result.affectedRows; i++) {
rowIds.push(i);
}
for (var i in persistentObjects) {
var persistentObject = persistentObjects[i];
persistentObject[persistentObject.idAttributeName()] = rowIds[i];
}
clb(null, persistentObjects);
});
(I pulled the values from an array of objects that I called persistentObjects.) Hope this helps. answered
Dec 29, 2015 at 2:24 3 This is a fast "raw-copy-paste" snipped to push a file column in mysql with node.js >= 11 250k row in few seconds 'use strict';
const mysql = require('promise-mysql');
const fs = require('fs');
const readline = require('readline');
async function run() {
const connection = await mysql.createConnection({
host: '1.2.3.4',
port: 3306,
user: 'my-user',
password: 'my-psw',
database: 'my-db',
});
const rl = readline.createInterface({ input: fs.createReadStream('myfile.txt') });
let total = 0;
let buff = [];
for await (const line of rl) {
buff.push([line]);
total++;
if (buff.length % 2000 === 0) {
await connection.query('INSERT INTO Phone (Number) VALUES ?', [buff]);
console.log(total);
buff = [];
}
}
if (buff.length > 0) {
await connection.query('INSERT INTO Phone (Number) VALUES ?', [buff]);
console.log(total);
}
console.log('end');
connection.close();
}
run().catch(console.log);
answered May 21, 2019 at 15:19 Manuel SpigolonManuel Spigolon 9,2375 gold badges46 silver badges68 bronze badges 2 If Ragnar's answer doesn't work for you. Here is probably why (based on my experience) - I wasn't using node-mysql package as shown my Ragnar. I was using
mysql package. They're different (if you didn't notice - just like me). But I'm not sure if it has anything to do with the ? not working, since it seemed to work for many folks using the mysql package. Try using a variable instead of ?
The following worked for me - var mysql = require('node-mysql');
var conn = mysql.createConnection({
...
});
var sql = "INSERT INTO Test (name, email, n) VALUES :params";
var values = [
['demian', '', 1],
['john', '', 2],
['mark', '', 3],
['pete', '', 4]
];
conn.query(sql, { params: values}, function(err) {
if (err) throw err;
conn.end();
});
Hope this helps someone. answered Feb 8, 2017 at 0:31
2 In case that needed here is how we solved insert of array request is from postman (You will look at "guests" ) {
"author_id" : 3,
"name" : "World War II",
"date" : "01 09 1939",
"time" : "16 : 22",
"location" : "39.9333635/32.8597419",
"guests" : [2, 3, 1337, 1942, 1453]
}
And how we scripted var express = require('express');
var utils = require('./custom_utils.js');
module.exports = function(database){
var router = express.Router();
router.post('/', function(req, res, next) {
database.query('INSERT INTO activity (author_id, name, date, time, location) VALUES (?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE name = VALUES(name), date = VALUES(date), time = VALUES(time), location = VALUES(location)',
[req.body.author_id, req.body.name, req.body.date, req.body.time, req.body.location], function(err, results, fields){
if(err){
console.log(err);
res.json({ status: utils.respondMSG.DB_ERROR });
}
else {
var act_id = results.insertId;
database.query('INSERT INTO act_guest (user_id, activity_id, status) VALUES ? ON DUPLICATE KEY UPDATE status = VALUES(status)',
[Array.from(req.body.guests).map(function(g){ return [g, act_id, 0]; })], function(err, results, fields){
if(err){
console.log(err);
res.json({ status: utils.respondMSG.DB_ERROR });
}
else {
res.json({
status: utils.respondMSG.SUCCEED,
data: {
activity_id : act_id
}
});
}
});
}
});
});
return router;
};
answered Feb 22, 2017 at 20:38 SamirSamir 6,0954 gold badges37 silver badges39 bronze badges I was having similar problem. It was just inserting one from the list of arrays. It worked after making the below changes. - Passed [params] to the query method.
- Changed the query from insert (a,b) into table1 values (?)
==> insert (a,b) into table1 values ? . ie. Removed the paranthesis around the question mark.
Hope this helps. I am using mysql npm. answered Mar 6, 2018 at 16:11 Few things I want to mention is that I'm using mysql package for making a connection with my database and what you saw below is
working code and written for insert bulk query. const values = [
[1, 'DEBUG', 'Something went wrong. I have to debug this.'],
[2, 'INFO', 'This just information to end user.'],
[3, 'WARNING', 'Warning are really helping users.'],
[4, 'SUCCESS', 'If everything works then your request is successful']
];
const query = "INSERT INTO logs(id, type, desc) VALUES ?";
const query = connection.query(query, [values], function(err, result) {
if (err) {
console.log('err', err)
}
console.log('result', result)
});
answered Nov 23, 2018 at 17:50 SagarSagar 3,8753 gold badges29 silver badges36 bronze badges Bulk insert in Node.js can be done using the below code. I have referred lots of blog for getting this work. please refer this link as well. https://www.technicalkeeda.com/nodejs-tutorials/insert-multiple-records-into-mysql-using-nodejs The working code. const educations = request.body.educations;
let queryParams = [];
for (let i = 0; i < educations.length; i++) {
const education = educations[i];
const userId = education.user_id;
const from = education.from;
const to = education.to;
const instituteName = education.institute_name;
const city = education.city;
const country = education.country;
const certificateType = education.certificate_type;
const studyField = education.study_field;
const duration = education.duration;
let param = [
from,
to,
instituteName,
city,
country,
certificateType,
studyField,
duration,
userId,
];
queryParams.push(param);
}
let sql =
"insert into tbl_name (education_from, education_to, education_institute_name, education_city, education_country, education_certificate_type, education_study_field, education_duration, user_id) VALUES ?";
let sqlQuery = dbManager.query(sql, [queryParams], function (
err,
results,
fields
) {
let res;
if (err) {
console.log(err);
res = {
success: false,
message: "Insertion failed!",
};
} else {
res = {
success: true,
id: results.insertId,
message: "Successfully inserted",
};
}
response.send(res);
});
Hope this will help you. answered Apr 11, 2020 at 0:26 If you want to insert object, use this: currentLogs = [
{ socket_id: 'Server', message: 'Socketio online', data: 'Port 3333', logged: '2014-05-14 14:41:11' },
{ socket_id: 'Server', message: 'Waiting for Pi to connect...', data: 'Port: 8082', logged: '2014-05-14 14:41:11' }
];
console.warn(currentLogs.map(logs=>[ logs.socket_id , logs.message , logs.data , logs.logged ]));
The output will be: [
[ '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'
]
]
Also, please check the documentation to know more about the map function.
sɐunıɔןɐqɐp 2,95515 gold badges34 silver badges38 bronze badges
answered Sep 17, 2020 at 11:12 Client.createClient = (clientReqData, result) =>{
var command = 'INSERT INTO client (name,email,phone,country_code,city,state,address,salaes_account_manager,internal_notes) VALUES (?,?,?,?,?,?,?,?,?)' ;
dbConn.query(command,[clientReqData.name,
clientReqData.email,clientReqData.phone,clientReqData.country_code,clientReqData.city,clientReqData.state,clientReqData.address,clientReqData.salaes_account_manager,clientReqData.internal_notes],(err,res)=>{
if(err){
console.log(err)
}else {
client_id = res.insertId;
var command = 'INSERT INTO client_contact_person (name, email ,phone,client_id) VALUES ?';
dbConn.query(command,
[clientReqData.contact.map(item => [item.name, item.email, item.phone,client_id])],
(err, res) => {
if(err) throw err
}
);
result(null,res);
}
})
} answered Sep 23, 2021 at 10:37 kushkush 4795 silver badges7 bronze badges Not the answer you're looking for? Browse other questions tagged
mysql node.js or ask your own question. |
