How to do bulk insert In mariadb using nodejs?

Question

Whenever i am trying to run my code this is showing the column count doesn't match error.

values=[
    [{id:12227722345,name:"dgssssdavgsgfv",pass:"cvhsssssadfvugod"}],
    [{id:12,name:"ddd",pass:"cvh"}]
   ];

c.query('insert into Hash.asn(userid,username,password) values (?,?,?)',[values],function(err,rows)
{
  if (err)
    console.log(err);

  c.query('commit');
  console.log(rows);

});

Error:

{ [Error: Column count doesn't match value count at row 1] code: 1136 }

I am not sure but just suggestion try with one value maybe the [] format is causing issue. — Neha
– Neha, Commented Jun 9, 2016 at 8:02
One value is working but i want to do with multiple value So can you help me how to do it? — Akash Sourav Nayak
– Akash Sourav Nayak, Commented Jun 9, 2016 at 8:04
just suggestion if its help. as i also not tried, why u keeping [] around the {} object ? any reason. if u remove it and try. — Neha
– Neha, Commented Jun 9, 2016 at 8:07
Then how will you send multiple values Like multiple id,multiple username at a time . — Akash Sourav Nayak
– Akash Sourav Nayak, Commented Jun 9, 2016 at 8:58
I think something like this - var values = [{1,'a','b'}, {2,'v','h'}]; insert into Hash.asn(userid,username,password) values (?,?,?)',[values] — Neha
– Neha, Commented Jun 9, 2016 at 9:41

jarrydrichards · Accepted Answer · 2019-02-12 11:27:39Z

9

In case anyone is still wondering this you can use connection.batch() to perform bulk queries.

connection.beginTransaction();
connection.query("INSERT INTO BASKET(customerId) values (?)", [1], (err, res) => {
   //must handle error if any
   const basketId = res.insertId;
   try {
     connection.batch("INSERT INTO basket_item(basketId, itemId) VALUES (?, ?)",[
         [basketId, 100],
         [basketId, 101],
         [basketId, 103],
         [basketId, 104],
         [basketId, 105]
     ]);
     //must handle error if any
     connection.commit();
     } catch (err) {
     connection.rollback();
     //handle error
     }
});

https://github.com/MariaDB/mariadb-connector-nodejs/blob/master/documentation/batch.md

answered Feb 12, 2019 at 11:27

jarrydrichards

1091 silver badge9 bronze badges

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

1 Comment

mercury Over a year ago

this answer is premium

Yunho Jang · Accepted Answer · 2018-11-08 03:09:13Z

-1

These days, As I know it, mariaDB module was not support bulk insert on the node.js.

https://mariadb.com/kb/en/library/connectornodejs-pipelining/

answered Nov 8, 2018 at 3:09

Yunho Jang

11

1 Comment

Tân Over a year ago

Welcome to StackOverflow! While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes.

Collectives™ on Stack Overflow

How to do bulk insert In mariadb using nodejs?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related