Insert Multiple Rows in SQLite Error (error code =

问题:

I am getting an error when executing the following query in SQLite Android

sDataBase.execSQL(query);

insert into Contacts(ID,FirstName,LastName,PhoneNumber,EmailId,Status) values('ae0caa6a-8ff6-d63f-0253-110b20ac2127','xxx','xxx','9008987887','[e-mail]','Yes'),('9afab56e-a18a-47f2-fd62-35c78d8e0d94','yyy','yyy','7890988909','[e-mail]','Yes'),('378d757a-ee60-07a4-e8bc-396b402c3270','zzz','zzz','9000898454','[e-mail]','Yes')

Note: This is executed fine in SQLServer, getting error in Android SQLite.

Error: sqlite returned: error code = 1, msg = near ",": syntax error, db=/data/data/myapp.contactmanager/databases/webview.db

EDIT 1: I have added space between values still getting error

insert into Contacts(ID, FirstName, LastName, PhoneNumber, EmailId,Status) values
('ae0caa6a-8ff6-d63f-0253-110b20ac2127', 'xxx', 'xxx','9008987887', '[e-mail]', 'Yes'), 
('9afab56e-a18a-47f2-fd62-35c78d8e0d94', 'yyy', 'yyy', '7890988909', '[e-mail]', 'Yes'), 
('378d757a-ee60-07a4-e8bc-396b402c3270', 'zzz', 'zzz', '9000898454', '[e-mail]', 'Yes')

Edit 2: Changed the query using Is it possible to insert multiple rows at a time in an SQLite database? still not working

INSERT INTO Contacts 
SELECT 'ae0caa6a-8ff6-d63f-0253-110b20ac2127' AS ID, 'xxx' AS FirstName, 'xxx' AS LastName, '9008987887' AS PhoneNumber, '[e-mail]' AS EmailId, 'Yes' AS Status, 
UNION SELECT '9afab56e-a18a-47f2-fd62-35c78d8e0d94', 'yyy', 'yyy', '7890988909', '[e-mail]', 'Yes' 
UNION SELECT '378d757a-ee60-07a4-e8bc-396b402c3270', 'zzz', 'zzz', '9000898454', '[e-mail]', 'Yes'

Edit 3: I have did a small mistake in the Edit 2. End of Sync status that is before UNION i have included comma, so that i got an error. Now removed and working fine.

INSERT INTO Contacts 
SELECT 'ae0caa6a-8ff6-d63f-0253-110b20ac2127' AS ID, 'xxx' AS FirstName, 'xxx' AS LastName, '9008987887' AS PhoneNumber, '[e-mail]' AS EmailId, 'Yes' AS Status 
UNION SELECT '9afab56e-a18a-47f2-fd62-35c78d8e0d94', 'yyy', 'yyy', '7890988909', '[e-mail]', 'Yes' 
UNION SELECT '378d757a-ee60-07a4-e8bc-396b402c3270', 'zzz', 'zzz', '9000898454', '[e-mail]', 'Yes'

回答1:

That's not how you insert multiple rows in SQLite. See this post for details.

If I were you, go with either the syntax in that question's answer OR, if you want to be able to insert a single row at once too, just do a standard INSERT INTO db (one, two) VALUES (one, two) and execute that once for each row you're inserting. Up to you!

回答2:

Insert Multiple Rows in SQLite Error

I really don't like your approach. It's so hardcored and it looks like spaghetti code. And your approach never will work because one insert can have only one values. So you are doing it incorrect. What about to use API insert() method that it directly designed for inserting?

Also from point of view about speed performance and security i recommend you to use TRANSACTION as well. But let's go to write some code especially for you.

public void insertMultiple(List<Contact> contacts) {
    Contact c = null;
    ContentValues values = new ContentValues();
    try {
        if (db != null) {
            db.beginTransaction();
            for (Contact c: contacts) {
                values.put(SQLConstants.FIRSTNAME, c.getFirstName());
                values.put(SQLConstants.LASTNAME, c.getLastName());
                ...
                db.insertOrThrow("Contacts", SQLConstants.FIRSTNAME, values);
                values.clear();
            }
            db.setTransactionSuccessful();
        }
    }
    finally {
        if (db != null && db.inTransaction()) {
            db.endTransaction();
        }
    }
}

Notes:

As i noticed you have table called Constacts so i suggest you to create own Object Contact that will represent your table on application layer where properties of object are equal to columns in table. I recommend you to use this approach because:

• It's not spagetthi code
• It's pretty faster
• It's much more safe

A few suggestions at the end:

execSQL() is not bad but i'm using it generally only when i create SQLiteOpenHelper subclass for creating and deleting and altering tables. There an usage is pretty suitable. But as main recommendations to you are:

• Everytime you are inserting, updating, deleting an usage of transaction is always very good practice, because except increase of speed performance(especially if you are insert great number of rows) is your works with database much more safe.

_{Only for info: I did a few tests when i inserted 1000, 10 000, 100 000 rows into SQLite and i can tell you that inserting of 100 000 rows took only 55,346 seconds. Inserting of 1 000 rows without transaction took even 73,398 seconds.}

• Everytime you are selecting, inserting, updating from one or more tables use placeholders i.e. parametrized statements and not hardcoded. With it, your queries will become safer, faster and better human-readable. In the case of joining always use join clause. It's best you can choose.

Update:

Here is working your code:

insert into Contacts(ID, FirstName, LastName, PhoneNumber, EmailId, Status) 
select 'someId' as ID, 'xxx' as FirstName, 'xxx' as LastName , '9008987887' as PhoneNumber , '[e-mail]' as EmaiId, 'Yes' as Status
union all select 'someId', 'xxx', 'xxx', '9008987887', '[e-mail]', 'Yes'
union all select 'someId', 'xxx', 'xxx', '9008987887', '[e-mail]', 'Yes'

Update 2:

As @Korniltsev Anatoly pointed out in SQLite there is a constraint SQLITE_MAX_COMPOUND_SELECT that means that you cannot use more than 500 unions at once.

回答3:

In addition to @Sajmon 's answer:

It is worth to mention that there is a limitation in sqlite - SQLITE_MAX_COMPOUND_SELECT which is 500 or greater on almost all devices. It means that you can not use more than 500 unions at once. If you'l use more - your sql query may fail.