I need to insert 10 rows to a sqlite3 table, and once the insertions are done, pass the 10 id's of the new rows to a callback function in an array.

My problem is that I can't figure out how to make a prepared statement perform multiple inserts at once. I found this post on how to do this with mySQL.

Bulk Inserts in mySQL

But this doesn't work with sqlite. My code is below:

params = [[1,2],[3,4],[5,6],[7,8]]

stmt = db.prepare("INSERT INTO test (num1, num2) VALUES (?)");
stmt.all([params], function(err, res) {
    if(err) {
        console.log(err);
        return;
    } else {
        createdIDs = []
        for (i in result) {
            createdIDs.push(result[i].id);
        }
        nextFunction(createdIDs);
    }
});

Which gives the following error:

Error: SQLITE_ERROR: 1 values for 2 columns
at Error (native)

The table's schema is like this:

db.run('CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY AUTOINCREMENT, num1 INTEGER NOT NULL, num2 INTEGER NOT NULL)')

Edit: Using Alvaro's solution, I now get this error message:

{ [Error: SQLITE_CONSTRAINT: NOT NULL constraint failed: test.num1] errno: 19, code: 'SQLITE_CONSTRAINT' }

I need to insert 10 rows to a sqlite3 table, and once the insertions are done, pass the 10 id's of the new rows to a callback function in an array.

My problem is that I can't figure out how to make a prepared statement perform multiple inserts at once. I found this post on how to do this with mySQL.

Bulk Inserts in mySQL

But this doesn't work with sqlite. My code is below:

params = [[1,2],[3,4],[5,6],[7,8]]

stmt = db.prepare("INSERT INTO test (num1, num2) VALUES (?)");
stmt.all([params], function(err, res) {
    if(err) {
        console.log(err);
        return;
    } else {
        createdIDs = []
        for (i in result) {
            createdIDs.push(result[i].id);
        }
        nextFunction(createdIDs);
    }
});

Which gives the following error:

Error: SQLITE_ERROR: 1 values for 2 columns
at Error (native)

The table's schema is like this:

db.run('CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY AUTOINCREMENT, num1 INTEGER NOT NULL, num2 INTEGER NOT NULL)')

Edit: Using Alvaro's solution, I now get this error message:

{ [Error: SQLITE_CONSTRAINT: NOT NULL constraint failed: test.num1] errno: 19, code: 'SQLITE_CONSTRAINT' }

• javascript
• node.js
• sqlite
• sql-insert
• bulkinsert

• are you sure you did just like the answer? – Alvaro Silvino Commented Jul 15, 2016 at 3:38
• @AlvaroJoao Yes, the code should be just like that other answer. The difference is that I'm using sqlite3 and not mysql – John Palmer Commented Jul 15, 2016 at 3:50
• just answered your question... pls upvote and mark as correct answer if I helped you – Alvaro Silvino Commented Jul 15, 2016 at 4:05
• VALUES (?) you still need to change to VALUES (?1,?2) – Alvaro Silvino Commented Jul 15, 2016 at 4:53
• Pls add to the question the table's CONSTRAINTs? – Alvaro Silvino Commented Jul 15, 2016 at 4:54

1 Answer 1

You have to enumerate the values in the order of their appearance:

db.run("INSERT INTO test (num1, num2) VALUES (?1,?2)");

That's why only one variable is detected instead of the expected two.

Reference here.

One more way to do it:

// create the table
db.run('CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY AUTOINCREMENT, num1 INTEGER NOT NULL, num2 INTEGER NOT NULL)')

var params = [[1,2],[3,4],[5,6],[7,8]];
db.serialize(function() {
    db.run("begin transaction");

    for (var i = 0; i < params.length; i++) {
        db.run("insert into data(num1, num2) values (?, ?)", params[i][0], params[i][1]);
    }

    db.run("mit");
});

reference: https://gist.github./NelsonMinar/2db6986d5b3cda8ad167