Insert multiple rows in SQLite

Question

I am trying to insert multiple rows in a SQLite (latest version) table but throws an error

got the idea from HERE and here is my sql query :

INSERT INTO "Track"
SELECT "Leonard Collections" AS "Album",
       "Instrumental" AS "Artist",
       "00:02:59.3800000" AS "Duration",
       "1/1/0001 12:00:00 AM" AS "ReleasedDate",
       "If You Love Me" AS "Title",
       "False" AS "IsPlayableOnLocal"
UNION
SELECT "Leonard Collections",
       "Instrumental",
       "00:02:56.6930000",
       "1/1/0001 12:00:00 AM",
       "Espoir",
       "False",
UNION
SELECT "Leonard Collections",
       "Instrumental",
       "00:03:51.6770000",
       "1/1/0001 12:00:00 AM",
       "Don't Cry For My Argentina",
       "False"

but it throws

SQL logic error or missing database

near "UNION": syntax error

this is my Table Structure

CREATE TABLE Track 
(
    ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , 
    Album VARCHAR(100) NULL , 
    Artist VARCHAR(255) NOT NULL DEFAULT "Artist Unknown", 
    Duration VARCHAR(255) NOT NULL , 
    LocalPath VARCHAR(255) NULL , 
    ReleasedDate DATE NOT NULL , 
    Title VARCHAR(255) NULL , 
    IsPlayableOnLocal INTEGER NOT NULL , 
    Rating VARCHAR(255) NULL
)

is there a problem with my query?

any help would be appreciated.

TIA

Possible duplicate of [Is it possible to insert multiple rows at a time in an SQLite database?](https://stackoverflow.com/questions/1609637/is-it-possible-to-insert-multiple-rows-at-a-time-in-an-sqlite-database) — StayOnTarget, Aug 16 '19 at 15:46

score 35 · Accepted Answer · answered Apr 17 '13 at 08:55

35

Since you mention latest version of SQLite, you should use multi-valued insert (supported by SQLite since version 3.7.11), like this:

INSERT INTO mytable (col1, col2, col3) VALUES
    (1, 2, "abc"),
    (2, 4, "xyz"),
    (3, 5, "aaa"),
    (4, 7, "bbb");

This is shorter, faster and less prone to errors. This syntax is also supported by some other databases (at least MySQL and PostgreSQL).

answered Apr 17 '13 at 08:55

mvp

102,692
13
114
144

Is SQLite version device dependent? – Stack Diego Dec 02 '14 at 11:28
1

Yes and no. SQLite is a library. If you are using (older) shared SQLite library, then it will depend on it. However, you can use static linking with newer version to avoid device dependency – mvp Dec 02 '14 at 11:36

score 3 · Answer 2 · answered Apr 17 '13 at 08:54

3

In your second union statement you have superflous ',' character after "False". That is most likely the problem.

answered Apr 17 '13 at 08:54

Devolus

21,012
12
62
109

Insert multiple rows in SQLite

2 Answers2

Linked