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

Question

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?

Tinsley

Answer 1

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.

Tinsley

Answer 2

Use the last_insert_rowid function:

SELECT last_insert_rowid();

Entozoic

How do I keep that value for three consecutive inserts? – Tinsley

Just insert the property row first, then the subproperty. If you need to remember the property ID for more than one insert, assign it to a variable before doing the subproperty inserts. – Entozoic

my problem is that this is a sqlite script and as far as I know, sqlite doesn't support user variables in scripts – Tinsley