Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to bulk insert in mySql and node.js using mysljs

Im not able to use bulk insert in my DB using node.js lib mysljs.

I followed answers from:

How do I do a bulk insert in mySQL using node.js

with no success.

var sql = "INSERT INTO resources (resource_container_id, name, title, extension, mime_type, size) VALUES ?";

var values = [
  [1, 'pic1', 'title1', '.png', 'image/png', 500], 
  [1, 'pic2', 'title2', '.png', 'image/png', 700]];

return connection.query(sql, [values], (result) => {
    if (err) throw err;
    connection.end();
});

I keep getting error:

 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near \'?\' at line 1' 

I also tried to promisify the query mehod using bluebird but with no success, I get same error again.

like image 747
Ivan Pandžić Avatar asked Dec 15 '16 18:12

Ivan Pandžić


People also ask

How should you do a bulk insertion into your MySQL project?

The general syntax of inserting bulk values in a table in MySQL is: INSERT INTO table_name VALUES (data), (data), (data); The explanation to the above general syntax is simple: Type the clause INSERT INTO and the table name in which you want to insert the data.

Does MySQL have bulk insert?

Using Bulk Insert Statement in MySQL. The INSERT statement in MySQL also supports the use of VALUES syntax to insert multiple rows as a bulk insert statement. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas.


1 Answers

You need to mark your keys with the grave accent (backtick) character, like this: `key`

Making your query like this:

var sql = "INSERT INTO resources (`resource_container_id`, `name`, `title`, `extension`, `mime_type`, `size`) VALUES ?";
like image 50
Unicco Avatar answered Oct 24 '22 12:10

Unicco