Search code examples
mysqlsqlsqlitemultiple-insert

Converting SQLite3 from MySQL database [Insert problem]


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.

Solution

  • 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.