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?

score 30 · Answer 1 · answered Nov 30 '09 at 20:18

30

Use the last_insert_rowid function:

SELECT last_insert_rowid();

answered Nov 30 '09 at 20:18

Ben S

68,394
30
171
212

How do I keep that value for three consecutive inserts? – Ed Marty Nov 30 '09 at 20:19
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. – Ben S Nov 30 '09 at 20:22
1

my problem is that this is a sqlite script and as far as I know, sqlite doesn't support user variables in scripts – Ed Marty Nov 30 '09 at 20:32

score 4 · Accepted Answer · answered Dec 03 '09 at 17:39

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.

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

2 Answers2

Linked