I am using dapper.net and sqlite. When I do this query only the first id is insert, the rest are ignored. How do I bulk insert?
insert or ignore into pair(key, value) select @key, (select value from list where id in @id_list)
A SELECT query without a FROM clause will generate a single record:
> SELECT 42; 42
When you put a subquery into that SELECT list, that subquery is a scalar subquery, which returns only a single value.
To return a constant value for each record in a query, you have to put that constant into the SELECT clause of that query:
INSERT OR IGNORE INTO pair(key, value) SELECT @key, value FROM list WHERE id IN (1,2,3,...)