Converting SQLite3 from MySQL database [Insert problem]

Question

I am trying to execute a SQL file (A dump file from MySQL database) inside a SQLite3 database. The problem I am facing is that the dump file has a multiple insert that is not handled by SQLite3. Here is an example:

INSERT INTO resultset_values (id, o_id, points, descrip, created, createdby, up, upby) 
VALUES 
(1,1,1,'string1','2011-03-29 11:21:00','user1',NULL,NULL),
(2,1,2,'string2','2011-03-29 11:21:00','user1',NULL,NULL);

This works fine in MySQL, but I can't make it work in SQLite3. Does someone have any idea of how to make multiples inserts in SQLite3? My database is pretty big, the example above is intended just to illustrate the problem.

Any particular reason why you'd want to move a database that's "pretty big" from MySQL to Sqlite? — NullUserException, Aug 25 '11 at 15:17
Yes, there is a reason for that. It is not that big, but anyways ... I am developing for mobile platforms, and so far, SQLite3 is the database they use :) — Joseandro Luiz, Aug 25 '11 at 15:18
Well, just wanted to make sure. Sqlite is not really meant to be used in applications that deal with large amounts of data and/or concurrent users. — NullUserException, Aug 25 '11 at 15:21
I am thinking about setting up a new webservice to help me doing this. It would be way better and faster ... but the user must to be connected to the internet. Thank you for the solicitude :) — Joseandro Luiz, Aug 25 '11 at 15:25

score 0 · Accepted Answer · edited May 23 '17 at 12:12

This has been answered here: Is it possible to insert multiple rows at a time in an SQLite database?

Apparently, this syntax is not supported in SQLite.

You have to turn

INSERT INTO resultset_values (id, o_id, points, descrip, created, createdby, up, upby) 
VALUES 
(1,1,1,'string1','2011-03-29 11:21:00','user1',NULL,NULL),
(2,1,2,'string2','2011-03-29 11:21:00','user1',NULL,NULL);

into:

INSERT INTO resultset_values (id, o_id, points, descrip, created, createdby, up, upby) 
VALUES 
(1,1,1,'string1','2011-03-29 11:21:00','user1',NULL,NULL);
INSERT INTO resultset_values (id, o_id, points, descrip, created, createdby, up, upby) 
VALUES 
(2,1,2,'string2','2011-03-29 11:21:00','user1',NULL,NULL);

You should be able to do that with your favorite Regex capable text editor.

Update

You can also try to convert that to this format:

INSERT INTO resultset_values
      SELECT 1,1,1,'string1','2011-03-29 11:21:00','user1',NULL,NULL
UNION SELECT 2,1,2,'string2','2011-03-29 11:21:00','user1',NULL,NULL
UNION ...

But personally, I think you'd have less work with the first option I presented.

Converting SQLite3 from MySQL database [Insert problem]

1 Answers1