"SQL logic error or missing database" -error on insert of multiple rows

Question

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"

Error :

SQL logic error or missing database

near "UNION": syntax error

Table :

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
)

What is wrong with my query?

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 36 · Accepted Answer · answered Apr 17 '13 at 08:55

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

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,661
13
66
113

score 0 · Answer 3 · answered Nov 04 '22 at 00:33

Successfully tested this into DB Browser for SQLite Execute SQL tab > SQL1 text editor field:

CREATE TABLE "FOODS" (
    "id"    INTEGER NOT NULL,
    "fruits"    TEXT,
    "tastes"    INTEGER UNIQUE,
    PRIMARY KEY("id" AUTOINCREMENT)
);
 
INSERT INTO "FOODS" (id, fruits) VALUES
    (1, "apple", 1),
    (2, "banana", 3),
    (3, "mango", 5),
    (4, "pear", 2),
    (5, "cherry", 4),
    (6, "pomegranate", 6);

https://pastebin.com/naXNRQEk

We must Create the table at the same time as the insertion (as with both statements above together).

Failed tests with trying to execute the INSERT statement alone on previously created from the Database Structure > Create Table button.

(That (alone) does not work in DB Browser for SQLite:)

INSERT INTO "FOODS" (id, fruits) VALUES
    (1, "apple", 1),
    (2, "banana", 3),
    (3, "mango", 5),
    (4, "pear", 2),
    (5, "cherry", 4),
    (6, "pomegranate", 6);

"SQL logic error or missing database" -error on insert of multiple rows

3 Answers3

Linked