Insert multiple rows into SQLite Error (error code = 1)

Question

I get an error while executing the following query in SQLite Android

sDataBase.execSQL(query); 

paste 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 '' him ',' him ',' 7890988909 ',' [e-mail] ',' Yes ​​'), (' 378d757a-ee60-07a4-e8bc- 396b402c3270 ',' ZZZ ',' ZZZ ',' 9000898454 ", 'ZZZ @ gmail.com', 'Yes')

Note. This runs fine in SQLServer, getting an 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 added a space between the values ​​still getting the 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 with Is it possible to insert multiple rows at once into the 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 made a small mistake in the status of Edit 2. End of Sync, which is equal to before UNION i have included comma , so 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' 

Answer 1

This is not how you insert multiple rows in SQLite. See this post for more details.

If I were you, use either the syntax in the answer to the question, or if you want to be able to insert one row at a time, just do the standard INSERT INTO db (one, two) VALUES (one, two) and do this for each row, re inserting. To you!

Answer 2

Insert multiple rows into SQLite error

I really don't like your approach. It is so complicated and it looks like spaghetti code. And your approach will never work, because one insert can only have one value . So you are doing it wrong. How to use the API insert() method, which is directly intended for insertion?

Also in terms of speed and security, you can also use TRANSACTION . But release it to write code specifically 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 a table called Constacts , so I suggest creating your own contact with objects that will represent your table at the application level, where the properties of the object are equal to the columns in the table. I recommend using this approach because:

• This is not a spaghetti code.
• It's pretty fast
• It is much safer.

A few suggestions at the end:

execSQL() not bad, but I use it in the general case only when I subclass SQLiteOpenHelper to create, delete, and modify tables. There use is quite suitable. But as the main recommendations for you:

• Each time you insert, update, delete use, a transaction is always a very good practice, because in addition to increasing (especially if you insert a large number of rows), your database operations are safer.

_{For information only: I did some tests when I entered 1000, 10,000, 100,000 rows in SQLite, and I can tell you that inserting 100,000 rows took only 55.346 seconds. Inserting 1,000 rows without a transaction took even 73,398 seconds.}

• Each time you select, insert, update from one or more tables, use placeholders , i.e. parameterized operators, not hardcoded. With it, your requests will become safer, faster and better for the person. When joining, always use the join clause. This is the best you can choose.

Update:

Your code works here:

 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 noted in SQLite, there is a SQLITE_MAX_COMPOUND_SELECT restriction, which means you cannot use more than 500 joins at the same time.

Answer 3

In addition to @Sajmon's answer:

It is worth noting that sqlite has a limitation - SQLITE_MAX_COMPOUND_SELECT , which is 500 or more on almost all devices. This means that you cannot use more than 500 unions at a time. If you use more, your sql query may fail.