Insert Multiple Rows in SQLite Error (error code = 1)

Question

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','xxx@gmail.com','Yes'),('9afab56e-a18a-47f2-fd62-35c78d8e0d94','yyy','yyy','7890988909','yyy@gmail.com','Yes'),('378d757a-ee60-07a4-e8bc-396b402c3270','zzz','zzz','9000898454','zzz@gmail.com','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', 'xxx@gmail.com', 'Yes'), 
('9afab56e-a18a-47f2-fd62-35c78d8e0d94', 'yyy', 'yyy', '7890988909', 'yyy@gmail.com', 'Yes'), 
('378d757a-ee60-07a4-e8bc-396b402c3270', 'zzz', 'zzz', '9000898454', 'zzz@gmail.com', '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, 'xxx@gmail.com' AS EmailId, 'Yes' AS Status, 
UNION SELECT '9afab56e-a18a-47f2-fd62-35c78d8e0d94', 'yyy', 'yyy', '7890988909', 'yyy@gmail.com', 'Yes' 
UNION SELECT '378d757a-ee60-07a4-e8bc-396b402c3270', 'zzz', 'zzz', '9000898454', 'zzz@gmail.com', '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, 'xxx@gmail.com' AS EmailId, 'Yes' AS Status 
UNION SELECT '9afab56e-a18a-47f2-fd62-35c78d8e0d94', 'yyy', 'yyy', '7890988909', 'yyy@gmail.com', 'Yes' 
UNION SELECT '378d757a-ee60-07a4-e8bc-396b402c3270', 'zzz', 'zzz', '9000898454', 'zzz@gmail.com', 'Yes'

check whether the table name in SQLite database and your code is right — user1835052, Mar 25 '13 at 11:27

Simon Dorociak · Answer 1 · 2013-03-26T08:28:00.530

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 , 'xxx@gmail.com' as EmaiId, 'Yes' as Status
union all select 'someId', 'xxx', 'xxx', '9008987887', 'xxx@gmail.com', 'Yes'
union all select 'someId', 'xxx', 'xxx', '9008987887', 'xxx@gmail.com', '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.

Thanks for your response. I have passed query from web page (Cross Platform Actions). May be you are right. but i want to clear errors in my query only — Ponmalar, Mar 25 '13 at 12:01
@Ponmalar you wont clear errors because your approach is not correct and never will work. you can have only one values per one insert.. not two, not three only one. — Simon Dorociak, Mar 25 '13 at 12:04
Please see the Edit 3. I have worked out this without union all — Ponmalar, Mar 26 '13 at 07:08

score 2 · Answer 2 · answered Mar 26 '13 at 07:12

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.

score 1 · Accepted Answer · edited May 23 '17 at 11:49

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!

edited May 23 '17 at 11:49

Community

1
1

answered Mar 25 '13 at 11:17

Cornholio

985
1
5
22

Please tell me what mistake in my input – Ponmalar Mar 25 '13 at 11:20
Inserting multiple values like that is valid in MySQL, but not SQLite; you have to use different syntax. Read the post I linked. – Cornholio Mar 25 '13 at 11:24
Interesting - in that case, use the answer provided by @Sajmon - Unless you really have to hardcode stuff, that's a much better way to do it. – Cornholio Mar 25 '13 at 12:49
1

I have solved my insert multiple row issue by the link that you have provided. Thank you – Ponmalar Mar 26 '13 at 07:11

Insert Multiple Rows in SQLite Error (error code = 1)

3 Answers3

Notes:

A few suggestions at the end:

Update:

Update 2:

Linked