for (var i=0; i<xml_length; i++) {
    db.execute('INSERT INTO myTable ("id", "value") VALUES ("'+xml.id+'", "'+xml.value+'")';
}

INSERT INTO myTable ("id", "values") VALUES ("1", "test"), ("2", "test"), ("3", "test");

BEGIN;
INSERT INTO myTable ("id", "values") VALUES ("1", "test")
INSERT INTO myTable ("id", "values") VALUES ("2", "test")
INSERT INTO myTable ("id", "values") VALUES ("3", "test")
COMMIT;

INSERT INTO myTable ("id", "values") VALUES ("1", "test", "2", "test", "3", "test");

2 Answers

• Accepted Answer

  Appcelerator uses a standard sqlite3 database engine. I'd suggest you start by looking at its online documentation for the insert statement. Each INSERT will create a single row unless you are using a SELECT clause instead of VALUES().

  To wrap several inserts within a transaction, I use this syntax:

  db.execute("BEGIN IMMEDIATE TRANSACTION");
  db.execute(sql);   // repeat as necessary
  db.execute(sql);
  db.execute(sql);
  db.execute("COMMIT TRANSACTION");
  

  You may want to peruse the syntax for various sqlite3 commands here.

• Thanks for your answer Doug.
  Your way to do it is really clean. And it's much faster than what I did before.

  While waiting for an answer, I found another way to do it. It's not as clean as yours but it seems a bit faster.
  The goal is to create a query such as :

  INSERT INTO myTable
  SELECT "1", "test"
  UNION
  SELECT "2", "test"
  ...
  

  It works, there seems to be a limit around 500 lines inserted, but it works.

  So I did a little Benchmark for inserting 1544 lines with the same datas (I did the test 5 times for each methods) :

  • Multiples INSERTs : 2500ms
  • Using BEGIN and COMMIT : 90ms
  • Using SELECT and UNION : 40ms

  Anyway, I think I'll use your method because your code is cleaner and much easier to maintain. Here is what I had to do :

  var newQuery = true, query = '';
  for (i=0; i<locations_length; i++) {
      if (newQuery) {
          query = 'INSERT INTO myTable (id, name, code)';
          newQuery = false;
      }
      else {
          query += ' UNION';
      }
  
      query += ' SELECT "'+values[i][0]+'", "'+values[i][1]+'", "'+values[i][2]+'"';
  
      if (i!=0 && i%499==0) {
          db.execute(query);
          newQuery = true;
      }
  }
  
  //executing remaining lines
  if (i%499!=0) {
      db.execute(query);
  }