[sql] 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'),
    ('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?

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

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

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

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

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)

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.

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

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 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); 

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)

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;

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.

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.

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

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.

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 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())

INSERT INTO tabela(coluna1,coluna2) 
SELECT 'texto','outro'
UNION ALL 
SELECT 'mais texto','novo texto';

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.