SQLite and multiple insert clean

Question

I would like to populate a freshly created Table in a SQLite DB.

In this table, some keys are references to other tables and I'd like not to hard-code these references -> I'm currently using a "mapping" table in order to fetch ids using names (~ constants emulation)

The problem is: this solution works but is very verbose

Minimal working example: (storing dictionary words, using foreign keys to a category table)

-- Tables creation

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

CREATE TABLE words(
  id INTEGER PRIMARY KEY,
  id_category INTEGER NOT NULL,
  name TEXT,
  FOREIGN KEY(id_category) REFERENCES categories(id)
);

CREATE TABLE CONSTANTS(
  name TEXT PRIMARY KEY,
  value INTEGER NOT NULL
);


INSERT INTO categories(name) VALUES("noun");
INSERT INTO CONSTANTS(name, value) VALUES("category_noun", last_insert_rowid());

INSERT INTO categories(name) VALUES("abreviation");
INSERT INTO CONSTANTS(name, value) VALUES("category_abreviation", last_insert_rowid());


INSERT INTO categories(name) VALUES("character");
INSERT INTO CONSTANTS(name, value) VALUES("category_character", last_insert_rowid());

And now, the core of the problem: too much verbose. In this example is only one foreign key, a few insert to illustrate the problem

INSERT INTO words(id_category, name) VALUES
   ((SELECT value FROM CONSTANTS WHERE name = "category_noun"),
    "hello"),

   ((SELECT value FROM CONSTANTS WHERE name = "category_abreviation"),
    "SO"),

   ((SELECT value FROM CONSTANTS WHERE name = "category_abreviation"),
    "user"),

   ((SELECT value FROM CONSTANTS WHERE name = "category_character"),
    "!")
;

I would like to have something looking like this pseudo-sqlite code:

-- same table creations as before

INSERT INTO words(id_category, name) VALUES
   -- Fetch constants once
    CAT_NOUM  = SELECT value FROM CONSTANTS WHERE name = "category_noum"),
    CAT_ABREV = SELECT value FROM CONSTANTS WHERE name = "category_abreviation"),
    CAT_CHAR = SELECT value FROM CONSTANTS WHERE name = "category_abreviation")
   )

   -- Fill the table, using constants
   (CAT_NOUM, "Hello"),
   (CAT_ABREV, "SO"),
   (CAT_NOUM, "user"),
   (CAT_CHAR, "SO"),
   ...
;

I'm wondering if

• There is already a SQLite solution to this problem
• I should use something like sed to replace a hard-coded string like __SED__CAT_NOUM with its greped value in the SQLite script
• Doing this stuff programmatically would be the right way

Answer 1

It is better to use INSERT...SELECT with UNION ALL instead of INSERT...VALUES:

INSERT INTO words(id_category, name) 
SELECT value, 'hello' FROM CONSTANTS WHERE name = 'category_noun' UNION ALL
SELECT value, 'SO' FROM CONSTANTS WHERE name = 'category_abreviation' UNION ALL
SELECT value, 'user' FROM CONSTANTS WHERE name = 'category_abreviation' UNION ALL
SELECT value, '!' FROM CONSTANTS WHERE name = 'category_character'; 

See the demo.

Or use Row Values to join to CONSTANTS:

INSERT INTO words(id_category, name) 
SELECT c.value, t.column2
FROM CONSTANTS C INNER JOIN (
  VALUES ('category_noun', 'hello'),
         ('category_abreviation', 'SO'),  
         ('category_abreviation', 'user'),
         ('category_character', '!')  
) t ON t.column1 = c.name; 

See the demo.

Results:

SELECT * FROM words;

| id  | id_category | name  |
| --- | ----------- | ----- |
| 1   | 1           | hello |
| 2   | 2           | SO    |
| 3   | 2           | user  |
| 4   | 3           | !     |