Menu
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?
446
The LAST_INSERT_ID() function returns the AUTO_INCREMENT id of the last row that has been inserted or updated in a table.
@@IDENTITY returns the id of the last thing that was inserted by your client's connection to the database. IDENT_CURRENT returns the last ID that was inserted by anyone. If some other app happens to insert another row at an unforunate time, you'll get the ID of that row instead of your one.
To get an ID of last inserted record, you can use this T-SQL: INSERT INTO Persons (FirstName) VALUES ('Joe'); SELECT ID AS LastID FROM Persons WHERE ID = @@Identity; You can use query like this inside stored procedure or as an ad-hoc query.
Use the last_insert_rowid function:
SELECT last_insert_rowid();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
