How can you use last_insert_rowid to insert multiple rows?

Let's say I have a database with two tables Persons and PhoneNumbers, where the PhoneNumbers table has a foreign key to Persons. If I want to insert a person with a phone number in a single transaction, I can write a query like this:

BEGIN TRANSACTION;
INSERT INTO Persons(Name) VALUES(...);
INSERT INTO PhoneNumbers(PersonForeignKey, Number) VALUES(last_insert_rowid(), ...);
END TRANSACTION;

But what if I want to insert a person with multiple phone numbers? The obvious way:

BEGIN TRANSACTION;
INSERT INTO Persons(Name) VALUES(...);
INSERT INTO PhoneNumbers(PersonForeignKey, Number) VALUES(last_insert_rowid(), ...);
INSERT INTO PhoneNumbers(PersonForeignKey, Number) VALUES(last_insert_rowid(), ...);
INSERT INTO PhoneNumbers(PersonForeignKey, Number) VALUES(last_insert_rowid(), ...);
END TRANSACTION;

won't work, of course, because for the second phone number, last_insert_rowid() will return the rowid of the first phone number instead of the person.

Is there a way to do this using basic SQL (SQLite, specifically)?

Conclusion

Apparently, there is no direct way to do this. I've made a few benchmarks with @Michal Powaga's suggestions and a few other ideas based on temporary tables, and the fastest way to do it seems to be something like this:

CREATE TEMPORARY TABLE IF NOT EXISTS Insert_PhoneNumbers(PersonForeignKey INTEGER, PhoneNumber VARCHAR);
DELETE FROM Insert_PhoneNumbers;
INSERT INTO Insert_PhoneNumbers(PhoneNumber) VALUES ('Phone 1');
INSERT INTO Insert_PhoneNumbers(PhoneNumber) VALUES ('Phone 2');

INSERT INTO Persons(Name) VALUES(...);

UPDATE Insert_PhoneNumbers SET PersonForeignKey=last_insert_rowid();

INSERT INTO PhoneNumbers(PersonForeignKey, Number)
   SELECT PersonForeignKey, PhoneNumber
   FROM Insert_PhoneNumbers

creating and updating a temporary table seems to be very fast (compared to queries on the Persons or PhoneNumbers-tables) and the insert speed won't depend on the number of persons/phone numbers already in the database, so that's the solution I chose.

Answers


You can store last_insert_rowid() after person insertion to temporary table or this might work too:

BEGIN TRANSACTION;
INSERT INTO Persons(Name) VALUES(...);

INSERT INTO PhoneNumbers(PersonForeignKey, Number) 
VALUES(last_insert_rowid(), 'number 1');

INSERT INTO PhoneNumbers(PersonForeignKey, Number) 
SELECT PersonForeignKey, 'number 2' 
FROM PhoneNumbers where PhonePrimaryKey = last_insert_rowid();

INSERT INTO PhoneNumbers(PersonForeignKey, Number) 
SELECT PersonForeignKey, 'number 3' 
FROM PhoneNumbers where PhonePrimaryKey = last_insert_rowid();

END TRANSACTION;

Need Your Help

Hide div when height is greater than certain amount of pixel

javascript jquery html css

I have a simple question, can I hide a div when height is greater than, for example, 1000px?

Inserting element in a linked list with Pascal

linked-list pascal freepascal

I've seen some algorithms designed to append an elment at the end of a linked list here and browsing other website, then I wrote a small procedure that i believe it should append a given element at...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.