1

I want to do a bulk insert to MYSQL table. According to this post, I'm using a 2D array. However, I kept getting MYSQL Syntax errors. Can anyone point me out?

Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL'

I have five columns in db, which has an auto-incrementing Id , update_date and create_date which I didn't put in the insert query. For update_date and create_date I set it to on update CURRENT_TIMESTAMP

var insert = 'INSERT INTO table2 (link, text) VALUES ?';
var res = [['www.google.com', 'google'],['www.bing.com', 'bing']];
var connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'XXX',
    database: 'dbname'
});
connection.connect();
connection.query(insert, [res], function(err) {
    if (err) {
        console.log(err)
        connection.end();
        throw err;
    }
    connection.end();
});
Community
  • 1
  • 1
catlovespurple
  • 682
  • 1
  • 7
  • 20

1 Answers1

0

Is the 'default' missing from your field definition ?

    update_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
peedeeaay
  • 116
  • 1
  • 7
  • Can you insert a record manually? ie. in mysql console or WorkBench run INSERT INTO table2 (link,text) VALUES ('www.google.com','google'); – peedeeaay Jan 19 '16 at 17:03
  • Yes I could insert manually. but the date values are null. I need to alter the table to set the default value not null. However, this didn't solve my problem.;-( – catlovespurple Jan 19 '16 at 17:12
  • So, you haven't altered the table definition? – peedeeaay Jan 19 '16 at 17:16
  • `alter table table2 modify created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP` I altered it already. – catlovespurple Jan 19 '16 at 17:18
  • `6 apple.com 2016-01-19 09:21:51 2016-01-19 09:21:51 apple` - sample record inserted manually – catlovespurple Jan 19 '16 at 17:22
  • Probably good to run it while monitoring using Workbench - Management - Client Connections to see what the insert query looks like. – peedeeaay Jan 19 '16 at 17:31