5 Replies Latest reply on Apr 21, 2008 9:52 PM by Ben Keen

    How to insert multiple rows into database at once?

    Ben Keen
      Hi,

      I was wondering if someone knew a way to insert multiple rows into a SQL Lite database table at once, using the same SQLStatement(); Inserting them one-by-one requires a great deal of code.

      Thanks!

      - Ben

        • 1. Re: How to insert multiple rows into database at once?
          Stephen Cox Level 1
          Wait. You want to be able to run multiple inserts at the same time into the same table?
          • 2. Re: How to insert multiple rows into database at once?
            Ben Keen Level 1
            Well, yeah. Like:

            INSERT INTO table ...
            INSERT INTO table ...
            INSERT INTO table ...

            or

            INSERT INTO table (col1, col2) VALUES
            (val1, val2),
            (val3, val4),
            (val5, val6)
            ...

            The problem is that performing a single query is very verbose, e.g.

            var insert = new air.SQLStatement();
            insert.sqlConnection = db.conn;
            var sql = "INSERT INTO table (col1, col2) VALUES (val1, val2)";
            insert.text = sql;
            insert.addEventListener(air.SQLEvent.RESULT, insertResult);
            insert.addEventListener(air.SQLErrorEvent.ERROR, insertError);
            insert.execute();
            • 3. Re: How to insert multiple rows into database at once?
              enorton@adobe Level 1
              Hi Ben,

              You should use parameters in your INSERT statement for the values. It will provide you better performance as well.

              I recommend reading the chapter in the on line docs on using SQL:
              http://livedocs.adobe.com/air/1/devappsflash/SQL_01.html

              var sql = "INSERT INTO table (col1, col2) VALUES (:val1, :val2)";
              insert.text = sql;
              insert.parameters[":val1"] = val1;
              insert.parameters[":val2"] = val2;

              I hope this helps!
              -Erica
              • 4. How to insert multiple rows into database at once?
                DonMitchinson Level 2
                Hi Ben:

                With SQLite you should be able to do it in one statement using the first example you provided - but not the second.
                Make sure to wrap in a transaction though.

                Haven't read the AIR SQL API carefully, but something like this should work:

                var insert = new air.SQLStatement();
                insert.sqlConnection = db.conn;
                var sql = "BEGIN TRANSACTION;"
                sql = sql + "INSERT INTO table (col1, col2) VALUES (A1, B1)";
                sql = sql + "INSERT INTO table (col1, col2) VALUES (A2, B2)";
                sql = sql + "INSERT INTO table (col1, col2) VALUES (A3, B3)";
                ...
                sql = sql + "INSERT INTO table (col1, col2) VALUES (An, Bn)";
                sql = sql + "END TRANSACTION;"
                insert.text = sql;
                insert.addEventListener(air.SQLEvent.RESULT, insertResult);
                insert.addEventListener(air.SQLErrorEvent.ERROR, insertError);
                insert.execute();

                Don
                • 5. Re: How to insert multiple rows into database at once?
                  Ben Keen Level 1
                  Erica - thanks for the tip! Good to know.

                  Don - yes, transactions was the key. Here's the syntax, for anyone that stumbles upon this thread.

                  quote:

                  var query = new air.SQLStatement();
                  query.sqlConnection = db.conn;
                  query.sqlConnection.begin();
                  query.text =
                  "CREATE TABLE IF NOT EXISTS mytable (" +
                  " table_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                  " table_col TEXT " +
                  ");" +
                  "INSERT INTO dg_cities (table_id, table_col) VALUES (1, 'one');" +
                  "INSERT INTO dg_cities (table_id, table_col) VALUES (2, 'two');" +
                  "INSERT INTO dg_cities (table_id, table_col) VALUES (3, 'elephant');";
                  query.sqlConnection.commit();
                  query.execute();