Insert Multiple Rows In One SQLite Insert Query

10 Jul 2014

Earlier this week, I found myself coming to an usecase of inserting multiple rows within one SQLite query. The idea is something like this in MySQL:

INSERT INTO 'tablename' ('column1', 'column2') VALUES
    ('data1', 'data2'),
    ('data1', 'data2'),
    ('data1', 'data2'),
    ('data1', 'data2');

The similar syntax is also available for SQLite 3.7.11 and above; however for older versions of SQLite, this syntax won’t work. Fortunately, there is another way to do the same thing, with the help of UNION or UNION ALL operator:

INSERT INTO 'tablename'
      SELECT 'data1' AS 'column1', 'data2' AS 'column2'
UNION SELECT 'data3', 'data4'
UNION SELECT 'data5', 'data6'
UNION SELECT 'data7', 'data8'

Of course, the second way has backward compatibility for the older versions of SQLite. Also notice that there is no performance advantage one way or the other (between the above mentioned two syntaxes).