bulk insert using sqlite and dapper

.net dapper sqlite3

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