How can I access the last inserted row ID within a SQL script?

relational, sqlite

Solution

Well, the solution I came up with used the `last_insert_rowid` function from Ben S:

INSERT INTO property VALUES(NULL,.....);
INSERT INTO property VALUES(NULL,.....);

   INSERT INTO subproperty VALUES(1,.....,-1);
   INSERT INTO subproperty VALUES(2,.....,-1);
   INSERT INTO subproperty VALUES(3,.....,-1);
INSERT INTO property VALUES(NULL,.....);
UPDATE subproperty SET fkPropertyId = (SELECT last_insert_rowid()) WHERE fkPropertyId=-1;

INSERT INTO property VALUES(NULL,.....);

Not sure if that's the best approach, but it works for me, and it doesn't use any extra tables for temporary data storage.

Problem

I'm using SQLite, and I have a table for properties, and a table for sub-properties. Each sub-property points to its parent using the fkPropertyId column. Right now, to create the initial database, I've got a script that looks something like this: ``` INSERT INTO property VALUES(1,.....); INSERT INTO property VALUES(2,.....); INSERT INTO property VALUES(3,.....); INSERT INTO subproperty VALUES(1,.....,3); INSERT INTO subproperty VALUES(2,.....,3); INSERT INTO subproperty VALUES(3,.....,3); INSERT INTO property VALUES(4,.....); ``` Now, I want to get rid of the hard-coded rowId, so it would be something like: ``` INSERT INTO property VALUES(NULL,.....); INSERT INTO property VALUES(NULL,.....); INSERT INTO property VALUES(NULL,.....); INSERT INTO subproperty VALUES(NULL,.....,X); INSERT INTO subproperty VALUES(NULL,.....,X); INSERT INTO subproperty VALUES(NULL,.....,X); INSERT INTO property VALUES(NULL,.....); ``` Where x refers to the last inserted rowId in the property table. Right now, that's ``` (SELECT MAX(rowId) FROM property) ``` Is there any better (and more technically accurate) way to write this script?

Original source