Question
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)
Popular Answer
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,...)
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why