In MySQL you can insert multiple rows like this:
INSERT INTO 'tablename' ('column1', 'column2') VALUES
('data1', 'data2'),
('data1', 'data2'),
('data1', 'data2'),
('data1', 'data2');
However, I am getting an error when I try to do something like this. Is it possible to insert multiple rows at a time in an SQLite database? What is the syntax to do that?
No, sqlite can not do this. The sqlite documentation is pretty good, by the way. It will also tell you that the only way to insert several row is when you use a select statement as the source of the data to be inserted.
According to this page it is not supported:
- 2007-12-03 : Multi-row INSERT a.k.a. compound INSERT not supported.
INSERT INTO table (col1, col2) VALUES
('row1col1', 'row1col2'), ('row2col1', 'row2col2'), ...
Actually, according to the SQL92 standard, a VALUES expression should be able to stand on itself. For example, the following should return a one-column table with three rows:
VALUES 'john', 'mary', 'paul';
Sqlite3 can't do that directly in SQL except via a SELECT, and while SELECT can return a "row" of expressions, I know of no way to make it return a phony column.
However, the CLI can do it:
.import FILE TABLE Import data from FILE into TABLE
.separator STRING Change separator used by output mode and .import
$ sqlite3 /tmp/test.db
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> create table abc (a);
sqlite> .import /dev/tty abc
1
2
3
99
^D
sqlite> select * from abc;
1
2
3
99
sqlite>
If you do put a loop around an INSERT, rather than using the CLI .import command, then be sure to follow the advice in the sqlite FAQ for INSERT speed:
By default, each INSERT statement is its own transaction. But if you surround multiple INSERT statements with BEGIN...COMMIT then all the inserts are grouped into a single transaction. The time needed to commit the transaction is amortized over all the enclosed insert statements and so the time per insert statement is greatly reduced.
Another option is to run PRAGMA synchronous=OFF. This command will cause SQLite to not wait on data to reach the disk surface, which will make write operations appear to be much faster. But if you lose power in the middle of a transaction, your database file might go corrupt.
Yes it is possible, but not with the usual comma-separated insert values.
Try this...
insert into myTable (col1,col2)
select aValue as col1,anotherValue as col2
union select moreValue,evenMoreValue
union...
Yes, it's a little ugly but easy enough to automate the generation of the statement from a set of values. Also, it appears you only need to declare the column names in the first select.
As the other posters have said, SQLite does not support this syntax. I don't know if compound INSERTs are part of the SQL standard, but in my experience they're not implemented in many products.
As an aside, you should be aware that INSERT performance in SQLite is improved considerably if you wrap multiple INSERTs in an explicit transaction.
You can't but I don't think you miss anything.
Because you call sqlite always in process, it almost doesn't matter in performance whether you execute 1 insert statement or 100 insert statements. The commit however takes a lot of time so put those 100 inserts inside a transaction.
Sqlite is much faster when you use parameterized queries (far less parsing needed) so I wouldn't concatenate big statements like this:
insert into mytable (col1, col2)
select 'a','b'
union
select 'c','d'
union ...
They need to be parsed again and again because every concatenated statement is different.
If you use the Sqlite manager firefox plugin, it supports bulk inserts from INSERT SQL statements.
Infact it doesn't support this, but Sqlite Browser does (works on Windows, OS X, Linux)
in mysql lite you cannot insert multiple values, but you can save time by opening connection only one time and then doing all insertions and then closing connection. It saves a lot of time