How to insert multiple rows in SQLite?

Question

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?

Lunarian

On bulk inserts: #1712131 – Pectize

Yes, Start from version 2012-03-20 (3.7.11), your syntax is supported. – Ontario

Answer 1

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.

Trematode

As a further note, sqlite only seems to support upto 500 such union selects per query so if you are trying to throw in more data than that you will need to break it up into 500 element blocks (sqlite.org/limits.html) – Icken

I don't think it's that much of a performance gain. SQLite's transactions are VERY fast with inserts. – Soledadsolely

Agreed: SQLite isn't the bottleneck, it's the overhead of individual ORM transactions (in my case, Ruby On Rails). So it's still a big win. – Trematode

Start from version 2012-03-20 (3.7.11), SQLite support OP's MySQL Style INSERT statement: INSERT INTO table(col1,col2) VALUES (var1,var2),(var3,var4),(var5,var6); – Ontario

How does this solution or the 3.7.11 solution compare to using transaction blocks? is it faster to insert into t values (data),(data),(data) or begin transaction; insert into t values (data);insert into t values (data);insert into t values (data);Commit;? – Skirmish

@Dan: That's an important question and warrants a proper benchmark! Of course, there are a lot of variables that are hard to control (e.g. where is the DB located?), but still a worthwhile task. – Trematode

As a further note: be careful! this syntax removes duplicate rows! use UNION ALL to avoid that (or for some minor performance gains). – Martlet

works perfectly for me.. You can add transaction after some inserts and will get more speedup.. For 2lakh insert statements, i got it done in 11s which was more than 2 minutes – Orvah

FYI : I did some benchmarks whether a single insert into with all values or multiple insert intos are faster (both using one single transaction) - one single insert into performs much better (~8ms vs ~15ms) in my case. see #68679488 – Cogwheel

In this query how to handle the escape string? – Pandiculation

@MahendranK: It would be easier to answer your question if you opened a new question (see [Ask Question] at the top of the page) describing what you've tried and what results you expected. – Trematode

Answer 2

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.

Beluga

please use UNION ALL not UNION. Except if you want to remove duplicates. – Filamentary

If you want IDs to auto-increment, give them a NULL value. – Yearwood

Answer 3

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.

3rd party edit

One sample would be

INSERT INTO MyTable 
    ( Column_foo, Column_CreatedOn) 
    VALUES 
        ('foo 1', '2023-02-20 14:10:00.001'),
        ('foo 2', '2023-02-20 14:10:00.002'),
        ('foo 3', '2023-02-20 14:10:00.003')    

Prepositive

I would say the diagramm allows multiple rows, as there is a closed loop with a comma outside the parentheses after VALUES. – Ti

@JohannesGerer They've updated this image since I embedded it. You can see the diagram at the time I embedded it at the Internet Archive. In fact, it was just two months ago that they added support for multiple rows in an insert, and just two days ago that they released a version with that change. I'll update my answer accordingly. – Prepositive

@Brian, could you please cite SQLite documentation text, that states that this IS possible? I reread insert docs 3 times and found nothing about inserting multiple rows, but only this picture (and nothing about comma in VALUES (...), (...)) :( – Fullblown

@Fullblown I linked to the commit in which this support was added, including test cases. You can see in the diagram (compare the IA link), that there is a loop around the expression after VALUES, indicating that it can be repeated separated by commas. And I linked to the release notes for the version adding the feature, which state "Enhance the INSERT syntax to allow multiple rows to be inserted via the VALUES clause." – Prepositive

@Fullblown It is unfortunate that they seem not to have updated the text of the documentation to match the diagram. But I can assure you, unless anything has changed in newer versions of SQLite, this is supported, as of SQLite 3.7.11. Have you checked to see if you are using that version or newer? I can run the following in SQLite 3.7.13 with no problems: create table foo (bar integer primary key, baz text); insert into foo (bar, baz) values (1, "blah"), (2, "stuff"); select * from foo;. – Prepositive

@BrianCampbell I believe you, just thought may be I was missing something not seeing description of the loop with comma in the text :) The fact is, I was trying to make some insert according to this picture, but it didn't worked (saying that error was in ,) but after I found your response I figured that problem was in sqlite version used in android (sqlite 3.7.11 is available only in android 4.1), so after that I wanted to find answer in official docs and didn't find it :) – Fullblown

Just wonder, why this case was still not described in text, more over, the official text says: The first form (with the "VALUES" keyword) creates a SINGLE new row... And this is annoying.. :( – Fullblown

@Fullblown I mentioned this to the SQLite maintainer, he has committed a fix which is available in the draft documentation. I'm guessing it will be in the official documentation as of the next release. – Prepositive

with python's sqlite, does executemany method with multiple INSERT statements inside provide the same performance benefit as the multi-row INSERT? or is it just syntactic sugar around the regular execute and to get any performance, we'll really need multi-row INSERT? – Mcdowell

Answer 4

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;

Icken

but this did not work in code, while it is working directly in SQLite manager. In code it only inserts 1st row :( – Demount

I'm using this style of insert in my code and it works perfectly. You only have to make sure you submit ALL the SQL at once. This was a huge speed increase for me but I'm curious of if the accepted answer is faster or slower then this? – Skirmish

This approach scales really well when modifying multiple tables within a single transaction, which I find myself doing often when batch loading a database. – Alvertaalves

Take note that this approach will not work if you need to use bindings. The SQLite3 Engine will assume that all of your bindings are to be applied on the first statement, and ignore the following statements. See this SO question for a more in detail explanation. – Bathe

Answer 5

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

Shipyard

There is no performance advantage one way or the other. - Could you tell me where did you saw that remark? I couldn't find it anywhere. – Inexpiable

Answer 6

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.

Ontario

Answer 7

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.

Fuddle

Answer 8

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.

Narcis

Answer 9

INSERT INTO TABLE_NAME 
            (DATA1, DATA2) 
VALUES      (VAL1, VAL2), 
            (VAL1, VAL2), 
            (VAL1, VAL2), 
            (VAL1, VAL2), 
            (VAL1, VAL2), 
            (VAL1, VAL2), 
            (VAL1, VAL2), 
            (VAL1, VAL2); 

Turkic

Answer 10

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.

Yazzie

If you look at the source code for SQLite's .import command, it's just a loop, reading a line from the input file (or tty) and then an INSERT statement for that line. Unfortunately not significantly improved efficiency. – Fluorescein

Answer 11

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)

Milden

Answer 12

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.

Jonquil

Great thanks that lead me to that other solution with DB Browser for SQLite: https://mcmap.net/q/41131/-quot-sql-logic-error-or-missing-database-quot-error-on-insert-of-multiple-rows – Corneliacornelian

Answer 13

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

Embarkation

Answer 14

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.

Pectize

Answer 15

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

Essa

Answer 16

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

Teheran

Answer 17

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

Lacquer

Answer 18

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

Snowberry

Answer 19

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.

Demount

Answer 20

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.

Macruran

Prepared statements are always a good idea, but not at all related to the question the OP is asking. He is asking what is the basic syntax for inserting multiple data in one statement. – Caldarium

Answer 21

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)

Aluin

Answer 22

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

Broadway

Answer 23

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

Preeminence

Answer 24

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

Officeholder

InsertHelper has been deprecated since API 17 – Diarmid

Answer 25

For example, there is person below:

CREATE TABLE person (
  id INTEGER PRIMARY KEY, 
  name TEXT,
  age INTEGER
);

Then, you can insert multiple rows to person table with these ways below. *For the INTEGER PRIMARY KEY column id, you can omit it or put NULL to automatically increment by 1:

INSERT INTO person (id, name, age) 
VALUES (1, 'John', 36), (2, 'David', 24), (3, 'Lisa', 18);

INSERT INTO person (name, age) 
VALUES ('John', 36), ('David', 24), ('Lisa', 18);

INSERT INTO person (id, name, age) 
VALUES (NULL, 'John', 36), (NULL, 'David', 24), (NULL, 'Lisa', 18);

INSERT INTO person 
VALUES (1, 'John', 36), (2, 'David', 24), (3, 'Lisa', 18);

INSERT INTO person
VALUES (NULL, 'John', 36), (NULL, 'David', 24), (NULL, 'Lisa', 18);

Then, you can get the same result below. *My answer explains how to show column names:

sqlite> .headers on  
sqlite> .mode box
sqlite> SELECT * FROM person;
┌────┬───────┬─────┐
│ id │ name  │ age │
├────┼───────┼─────┤
│ 1  │ John  │ 36  │
│ 2  │ David │ 24  │
│ 3  │ Lisa  │ 18  │
└────┴───────┴─────┘

Amadavat

Answer 26

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.

Silvestro

This does not work if you want to insert useful data. – Whitnell

@Whitnell its not true. you can mix it with random dates and ids however you want. – Silvestro