In MySQL I'd use
INSERT INTO `mytable` (`col1`, `col2`)
VALUES (1, 'aaa'), (2, 'bbb');
but this causes an error in SQLite. What is the correct syntax for SQLite?
-
1As long as all inserts run in the same transaction, it will be very fast. If using a prepared statement the parsing/prep overhead that needs to be done by SQLite will be minimized. So ... it's not really needed and makes more sense in an engine that has batch triggers, etc.user166390– user1663902012-04-06 03:47:37 +00:00Commented Apr 6, 2012 at 3:47
Add a comment
|
3 Answers
This has already been answered before here: Is it possible to insert multiple rows at a time in an SQLite database?
To answer your comment to OMG Ponies answer:
As of version 3.7.11 SQLite does support multi-row-insert. Richard Hipp comments:
"The new multi-valued insert is merely syntactic suger (sic) for the compound insert.
There is no performance advantage one way or the other."
Comments
Use a UNION:
INSERT INTO `mytable`
(`col1`, `col2`)
SELECT 1, 'aaa'
UNION ALL
SELECT 2, 'bbb'
UNION ALL is quicker than UNION, because UNION removes duplicates -- UNION ALL does not.
2 Comments
Ivan
Do you mean this can be any better than inserting every row with a separate INSERT? Using the example I've shown with MySQL can actually speed imports up dramatically as compared to using individual inserts for every record.
OMG Ponies
@Ivan: Yes, because INSERT writes to the transaction log (depending on logging level, but if you want point in time restoration...). So a single insert (or update for that matter) statement can be much quicker. For SQL Server, I've heard some say you 10-20 UNION's, maximum.
Start from version 2012-03-20 (3.7.11), sqlite support the following INSERT syntax:
INSERT INTO 'tablename' ('column1', 'column2') VALUES
('data1', 'data2'),
('data3', 'data4'),
('data5', 'data6'),
('data7', 'data8');
Read documentation: http://www.sqlite.org/lang_insert.html
