Is it possible to insert multiple rows at a time in an SQLite database?
In MySQL you can insert multiple rows like this:
INSERT INTO 'tablename' ('column1', 'column2') VALUES
('data1', 'data2'),
开发者_Python百科 ('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?
update
As BrianCampbell points out here, SQLite 3.7.11 and above now supports the simpler syntax of the original post. However, the approach shown is still appropriate if you want maximum compatibility across legacy databases.
original answer
If I had privileges, I would bump river's reply: You can insert multiple rows in SQLite, you just need different syntax. To make it perfectly clear, the OPs MySQL example:
INSERT INTO 'tablename' ('column1', 'column2') VALUES
('data1', 'data2'),
('data1', 'data2'),
('data1', 'data2'),
('data1', 'data2');
This can be recast into SQLite as:
INSERT INTO 'tablename'
SELECT 'data1' AS 'column1', 'data2' AS 'column2'
UNION ALL SELECT 'data1', 'data2'
UNION ALL SELECT 'data1', 'data2'
UNION ALL SELECT 'data1', 'data2'
a note on performance
I originally used this technique to efficiently load large datasets from Ruby on Rails. However, as Jaime Cook points out, it's not clear this is any faster wrapping individual INSERTs within a single transaction:
BEGIN TRANSACTION;
INSERT INTO 'tablename' table VALUES ('data1', 'data2');
INSERT INTO 'tablename' table VALUES ('data3', 'data4');
...
COMMIT;
If efficiency is your goal, you should try this first.
a note on UNION vs UNION ALL
As several people commented, if you use UNION ALL (as shown above), all rows will be inserted, so in this case, you'd get four rows of data1, data2. If you omit the ALL, then duplicate rows will be eliminated (and the operation will presumably be a bit slower). We're using UNION ALL since it more closely matches the semantics of the original post.
in closing
P.S.: Please +1 river's reply, as it presented the solution first.
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.
Yes, as of SQLite 3.7.11 this is supported in SQLite. From the SQLite documentation:
(when this answer was originally written, this was not supported)
For compatibility with older versions of SQLite, you can use the trick suggested by andy and fearless_fool using UNION, but for 3.7.11 and later the simpler syntax described in here should be preferred.
I wrote some ruby code to generate a single 500 element multi-row insert from a series of insert statements which was considerably faster than running the individual inserts. Then I tried simply wrapping the multiple inserts into a single transaction and found that I could get the same kind of speed up with considerably less code.
BEGIN TRANSACTION;
INSERT INTO table VALUES (1,1,1,1);
INSERT INTO table VALUES (2,2,2,2);
...
COMMIT;
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';
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."
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
PS: Please +1 to Brian Campbell's reply/answer. not mine! He presented the solution first.
Yes, sql can do this, but with a different syntax. The sqlite documentation is pretty good, by the way. It will also tell you that the only way to insert several row is use a select statement as the source of the data to be inserted.
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.
INSERT INTO TABLE_NAME
(DATA1, DATA2)
VALUES (VAL1, VAL2),
(VAL1, VAL2),
(VAL1, VAL2),
(VAL1, VAL2),
(VAL1, VAL2),
(VAL1, VAL2),
(VAL1, VAL2),
(VAL1, VAL2);
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.
Alex is correct: the "select ... union" statement will lose the ordering which is very important for some users. Even when you insert in a specific order, sqlite changes things so prefer to use transactions if insert ordering is important.
create table t_example (qid int not null, primary key (qid));
begin transaction;
insert into "t_example" (qid) values (8);
insert into "t_example" (qid) values (4);
insert into "t_example" (qid) values (9);
end transaction;
select rowid,* from t_example;
1|8
2|4
3|9
fearless_fool has a great answer for older versions. I just wanted to add that you need to make sure you have all the columns listed. So if you have 3 columns, you need to make sure select acts on 3 columns.
Example: I have 3 columns but I only want to insert 2 columns worth of data. Assume I don't care about the first column because it's a standard integer id. I could do the following...
INSERT INTO 'tablename'
SELECT NULL AS 'column1', 'data1' AS 'column2', 'data2' AS 'column3'
UNION SELECT NULL, 'data3', 'data4'
UNION SELECT NULL, 'data5', 'data6'
UNION SELECT NULL, 'data7', 'data8'
Note: Remember the "select ... union" statement will lose the ordering. (From AG1)
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.
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
The problem with using transaction is that you lock the table also for reading. So if you have really much data to insert and you need to access to your data, for exemple a preview or so, this way doesn't work well.
The problem with the other solution is that you lose the order of the inserting
insert into mytable (col)
select 'c'
union
select 'd'
union
select 'a'
union
select 'b';
In the sqlite the data will be store a,b,c,d...
As of version 3.7.11 SQLite does support multi-row-insert. Richard Hipp comments:
I'm using 3.6.13
I command like this:
insert into xtable(f1,f2,f3) select v1 as f1, v2 as f2, v3 as f3
union select nextV1+, nextV2+, nextV3+
With 50 records inserted at a time, it takes only a second or less.
It's true using sqlite to insert multiple rows at a time is very possible. By @Andy wrote.
thanks Andy +1
Simple. Self explanatory.
Test on version 3.36.0 11/20/21.
CREATE TEMP TABLE x (col1 TEXT, col2 TEXT, col3 TEXT);
INSERT INTO x
VALUES
('xx','yy','cc'),
('xx','yy','cc'),
('xx','yy','cc'),
('xx','yy','cc'),
('xx','yy','cc'),
('xx','yy','cc');
SELECT * FROM x;
Output:
col1|col2|col3|
----+----+----+
xx |yy |cc |
xx |yy |cc |
xx |yy |cc |
xx |yy |cc |
xx |yy |cc |
xx |yy |cc |
Version check:
SELECT sqlite_version();
Output:
sqlite_version()|
----------------+
3.36.0 |
Some may say - all the ".. UNION .." answers are outdated; nevertheless, they are very useful. Sometime we all get on our desks "the blast from the past" and then the 15 year old note saves our day.
INSERT INTO tabela(coluna1,coluna2)
SELECT 'texto','outro'
UNION ALL
SELECT 'mais texto','novo texto';
I'm surprised that no one has mentioned prepared statements. Unless you are using SQL on its own and not within any other language, then I would think that prepared statements wrapped in a transaction would be the most efficient way of inserting multiple rows.
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)
On sqlite 3.7.2:
INSERT INTO table_name (column1, column2)
SELECT 'value1', 'value1'
UNION SELECT 'value2', 'value2'
UNION SELECT 'value3', 'value3'
and so on
I am able to make the query dynamic. This is my table:
CREATE TABLE "tblPlanner" ("probid" text,"userid" TEXT,"selectedtime" DATETIME,"plannerid" TEXT,"isLocal" BOOL,"applicationid" TEXT, "comment" TEXT, "subject" TEXT)
and I'm getting all data through a JSON, so after getting everything inside an NSArray I followed this:
NSMutableString *query = [[NSMutableString alloc]init];
for (int i = 0; i < arr.count; i++)
{
NSString *sqlQuery = nil;
sqlQuery = [NSString stringWithFormat:@" ('%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@'),",
[[arr objectAtIndex:i] objectForKey:@"plannerid"],
[[arr objectAtIndex:i] objectForKey:@"probid"],
[[arr objectAtIndex:i] objectForKey:@"userid"],
[[arr objectAtIndex:i] objectForKey:@"selectedtime"],
[[arr objectAtIndex:i] objectForKey:@"isLocal"],
[[arr objectAtIndex:i] objectForKey:@"subject"],
[[arr objectAtIndex:i] objectForKey:@"comment"],
[[NSUserDefaults standardUserDefaults] objectForKey:@"applicationid"]
];
[query appendString:sqlQuery];
}
// REMOVING LAST COMMA NOW
[query deleteCharactersInRange:NSMakeRange([query length]-1, 1)];
query = [NSString stringWithFormat:@"insert into tblPlanner (plannerid, probid, userid, selectedtime, isLocal, applicationid, subject, comment) values%@",query];
And finally the output query is this:
insert into tblPlanner (plannerid, probid, userid, selectedtime, isLocal, applicationid, subject, comment) values
<append 1>
('pl1176428260', '', 'US32552', '2013-06-08 12:00:44 +0000', '0', 'subj', 'Hiss', 'ap19788'),
<append 2>
('pl2050411638', '', 'US32552', '2013-05-20 10:45:55 +0000', '0', 'TERI', 'Yahoooooooooo', 'ap19788'),
<append 3>
('pl1828600651', '', 'US32552', '2013-05-21 11:33:33 +0000', '0', 'test', 'Yest', 'ap19788'),
<append 4>
('pl549085534', '', 'US32552', '2013-05-19 11:45:04 +0000', '0', 'subj', 'Comment', 'ap19788'),
<append 5>
('pl665538927', '', 'US32552', '2013-05-29 11:45:41 +0000', '0', 'subj', '1234567890', 'ap19788'),
<append 6>
('pl1969438050', '', 'US32552', '2013-06-01 12:00:18 +0000', '0', 'subj', 'Cmt', 'ap19788'),
<append 7>
('pl672204050', '', 'US55240280', '2013-05-23 12:15:58 +0000', '0', 'aassdd', 'Cmt', 'ap19788'),
<append 8>
('pl1019026150', '', 'US32552', '2013-06-08 12:15:54 +0000', '0', 'exists', 'Cmt', 'ap19788'),
<append 9>
('pl790670523', '', 'US55240280', '2013-05-26 12:30:21 +0000', '0', 'qwerty', 'Cmt', 'ap19788')
which is running well through code also and I'm able to save everything in SQLite successfully.
Before this i made UNION query stuff dynamic but that started giving some syntax error. Anyways, this is running well for me.
I have a query like below, but with ODBC driver SQLite has an error with "," it says. I run vbscript in HTA (Html Application).
INSERT INTO evrak_ilac_iliskileri (evrak_id, ilac_id,
baglayan_kullanici_id, tarih) VALUES (4150,762,1,datetime()),
(4150,9770,1,datetime()),(4150,6609,1,datetime()),(4150,3628,1,datetime()),
(4150,9422,1,datetime())
you can use InsertHelper, it is easy and fast
documentation: http://developer.android.com/reference/android/database/DatabaseUtils.InsertHelper.html
tutorial: http://www.outofwhatbox.com/blog/2010/12/android-using-databaseutils-inserthelper-for-faster-insertions-into-sqlite-database/
Edit: InsertHelper is deprecated as of API Level 17
If you are using bash shell you can use this:
time bash -c $'
FILE=/dev/shm/test.db
sqlite3 $FILE "create table if not exists tab(id int);"
sqlite3 $FILE "insert into tab values (1),(2)"
for i in 1 2 3 4; do sqlite3 $FILE "INSERT INTO tab (id) select (a.id+b.id+c.id)*abs(random()%1e7) from tab a, tab b, tab c limit 5e5"; done;
sqlite3 $FILE "select count(*) from tab;"'
Or if you are in sqlite CLI, then you need to do this:
create table if not exists tab(id int);"
insert into tab values (1),(2);
INSERT INTO tab (id) select (a.id+b.id+c.id)*abs(random()%1e7) from tab a, tab b, tab c limit 5e5;
INSERT INTO tab (id) select (a.id+b.id+c.id)*abs(random()%1e7) from tab a, tab b, tab c limit 5e5;
INSERT INTO tab (id) select (a.id+b.id+c.id)*abs(random()%1e7) from tab a, tab b, tab c limit 5e5;
INSERT INTO tab (id) select (a.id+b.id+c.id)*abs(random()%1e7) from tab a, tab b, tab c limit 5e5;
select count(*) from tab;
How does it work?
It makes use of that if table tab:
id int
------
1
2
then select a.id, b.id from tab a, tab b returns
a.id int | b.id int
------------------
1 | 1
2 | 1
1 | 2
2 | 2
and so on. After first execution we insert 2 rows, then 2^3=8. (three because we have tab a, tab b, tab c)
After second execution we insert additional (2+8)^3=1000 rows
Aftern thrid we insert about max(1000^3, 5e5)=500000 rows and so on...
This is the fastest known for me method of populating SQLite database.
加载中,请稍侯......
精彩评论