How can I return inserted ids for multiple rows in SQLite?

Question

Given a table:

CREATE TABLE Foo(
    Id INTEGER PRIMARY KEY AUTOINCREMENT,
    Name TEXT
);

How can I return the ids of the multiple rows inserted at the same time using:

INSERT INTO Foo (Name) VALUES
('A'),
('B'),
('C');

I am aware of last_insert_rowid() but I have not found any examples of using it for multiple rows.

What I am trying to achieve can bee seen in this SQL Server example:

DECLARE @InsertedRows AS TABLE (Id BIGINT);
INSERT INTO [Foo] (Name) OUTPUT Inserted.Id INTO @InsertedRows VALUES 
    ('A'),
    ('B'),
    ('C');
SELECT Id FROM @InsertedRows;

Any help is very much appreciated.

Answer 1

This is not possible. If you want to get three values, you have to execute three INSERT statements.

Answer 2

Given SQLite3 locking:

An EXCLUSIVE lock is needed in order to write to the database file. Only one EXCLUSIVE lock is allowed on the file and no other locks of any kind are allowed to coexist with an EXCLUSIVE lock. In order to maximize concurrency, SQLite works to minimize the amount of time that EXCLUSIVE locks are held.

And how Last Insert Rowid works:

...returns the rowid of the most recent successful INSERT into a rowid table or virtual table on database connection D.

It should be safe to assume that while a writer executes its batch INSERT to a ROWID-table there can be no other writer to make the generated primary keys non-consequent. Thus the insert primary keys are [lastrowid - rowcount + 1, lastrowid]. Or in Python SQLite3 API:

cursor.execute(...)  # multi-VALUE INSERT
assert cursor.rowcount == len(values)
lastrowids = range(cursor.lastrowid - cursor.rowcount + 1, cursor.lastrowid + 1)

In normal circumstances when you don't mix provided and expected-to-be-generated keys or as AUTOINCREMENT-mode documentation states:

The normal ROWID selection algorithm described above will generate monotonically increasing unique ROWIDs as long as you never use the maximum ROWID value and you never delete the entry in the table with the largest ROWID.

The above should work as expected.

This Python script can be used to test correctness of the above for multi-threaded and multi-process setup.

Other databases

For instance, MySQL InnoDB (at least in default innodb_autoinc_lock_mode = 1 "consecutive" lock mode) works in similar way (though obviously in much more concurrent conditions) and guarantees that inserted PKs can be inferred from lastrowid:

"Simple inserts" (for which the number of rows to be inserted is known in advance) avoid table-level AUTO-INC locks by obtaining the required number of auto-increment values under the control of a mutex (a light-weight lock) that is only held for the duration of the allocation process, not until the statement completes