1

I want to store in a SQLite database a lot of tuples of the form (word1, word2, score). Since there are many repetitions of word1 and word2, to save space and have something more compact, I created three tables with the fields:

  • Table0 (id1, id2, score)
  • Table1 (id, word1)
  • Table2 (id, word2)

where id1 and id2 are the foreign keys referring to the primary key ID of Table1 and Table2.

At the beginning I was doing this with just one table (word1, word2, score) and since I have a lot of rows, to make the insertion faster, I was using INSERT INTO with UNION SELECT as this is explained here: Is it possible to insert multiple rows at a time in an SQLite database?

Is there a similar way to insert multiple rows at the same time with SQLite when we have foreign key constraints?

Community
  • 1
  • 1
dhokas
  • 1,771
  • 2
  • 13
  • 22
  • A single INSERT statement inserts into a single table. But why do you think that using UNION would be faster? – CL. Apr 25 '15 at 22:06
  • Insert with batch instead of row by row is faster from what I saw. Usually I use MySQL and for multiple inserts I do "INSERT INTO table ('col1', 'col2') VALUES ('value1', 'value2'), ('value1', 'value2')". For SQLite, the equivalent I found was to use UNION, I find this weird but it works well (150,000 inserts /sec with batch of size 500). Indeed, it looks like I need several INSERT statements if I insert in 3 tables, but I don't know how do that.. – dhokas Apr 26 '15 at 00:23
  • You know that SQLite has been supporting the same syntax for three years? And what is the problem with writing three INSERT statements? – CL. Apr 26 '15 at 06:40
  • What about the syntax? The problem is that it is too slow. Basically I am looking for something like the second answer proposed here: http://dba.stackexchange.com/questions/46410/how-do-i-insert-a-row-which-contains-a-foreign-key for INSERT missing FK rows at the same time – dhokas Apr 26 '15 at 07:11
  • SQLite supports CTEs since version 3.8.3. – CL. Apr 26 '15 at 08:11

0 Answers0