I want to insert into two tables at a time.
This needs to be done this way.
Insert single row into one table, get the auto incremented primary key and insert multiple rows into other table, using the primary key value I got from the first table as reference.
What is the best way to do this?
SQLite has the built-in last_insert_rowid() function, but its return value would change after the next insertion.
You have to read the value in your program, and insert it manually:
db.execute("INSERT INTO People(Name) VALUES (?)", ["Joe"])
id = db.last_insert_rowid
db.execute("INSERT INTO Pets(Owner, Name) VALUES (?,?)", [id, "Fluffy"])
db.execute("INSERT INTO Pets(Owner, Name) VALUES (?,?)", [id, "Spot"])
It depends on how you're incrementing the key. If you are storing the current "next key value" in another table, then first extract the next key, and use it for all your insert statements. Finally increment the "next key value" in the other table.
