SQLite: How to get the “autoincrement” value from the last insert

SQLite autoincrement FAQ: How do I get the autoincrement value from my last SQLite INSERT command?

Solution

Get the integer value of the primary key field from the last insert into an autoincrement field using a SQLite function named last_insert_rowid(), as shown in the example below.

Example: How to get the SQLite autoincrement (primary key) value after an insert

Here’s a short, complete example of how this works.

First, let’s assume we have a SQLite database table defined like this:

CREATE TABLE salespeople (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    commission_rate REAL NOT NULL
);

As you can see, the id field is the PRIMARY KEY field for this SQLite table. (Note that this field is referred to as an autoincrement field, serial field, or identity column in other databases I have worked with.)

Next, INSERT a record into this table, passing a null value into the SQLite autoincrement field:

sqlite> INSERT INTO salespeople VALUES (null, 'Fred', 'Flinstone', 10.0);

Now, just use the SQLite last_insert_rowid() function to get the value of the SQLite autoincrement field that was just generated:

sqlite> select last_insert_rowid();
2

In this case the result was 2, because this was the second record I’ve inserted into this table. I can verify that with a SQL SELECT statement:

sqlite> select * from salespeople;

1|Barney|Rubble|10.0
2|Fred|Flinstone|10.0