Insert into multiple tables SQLITE

Question

How can one insert a row into one table and use the id generated from that row into another row so that there can be a transaction around the whole thing? The problem I'm having is that there is massive db latency because I have 100k records, but I can only transact 1-100 at a time.

INSERT INTO foo;
INSERT into bar(foo_id) VALUES (last_foo_id)
INSERT into bar(foo_id) VALUES (last_foo_id)
INSERT into bar(foo_id) VALUES (last_foo_id)
INSERT into bar(foo_id) VALUES (last_foo_id)
INSERT into bar(foo_id) VALUES (last_foo_id)
INSERT INTO foo;
INSERT into bar(foo_id) VALUES (last_foo_id)
INSERT into bar(foo_id) VALUES (last_foo_id)
INSERT into bar(foo_id) VALUES (last_foo_id)
INSERT into bar(foo_id) VALUES (last_foo_id)
INSERT into bar(foo_id) VALUES (last_foo_id)

just how many bar records are you inserting? start a transaction, insert into foo, do the multiple bar inserts, then commit. start a new transactions, do another foo, then the bars, commit, etc... — Marc B
– Marc B, Commented Jun 5, 2013 at 15:54
Yes. Already doing that. Still bottlenecked. I'm doing 99% inserts into bar. — Bluebaron
– Bluebaron, Commented Jun 5, 2013 at 15:56
What is your client SQLite library and/or language? Is it Perl, Python, SQLite C/API, or anything else? — mvp
– mvp, Commented Jun 6, 2013 at 7:04

CL. · Accepted Answer · 2013-06-05 18:05:38Z

3

After inserting the foo record, read its ID with sqlite3_last_insert_rowid (or whatever your database wrapper uses for that), and use that value in all the following bar insert statements:

db.execute("BEGIN TRANSACTION")
db.execute("INSERT INTO foo ...")
foo_id = db.last_insert_rowid
db.execute("INSERT INTO bar(foo_id) VALUES (?)", [foo_id])
...
db.execute("COMMIT TRANSACTION")

edited Jun 5, 2013 at 18:05

answered Jun 5, 2013 at 16:28

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Bluebaron Over a year ago

How can I wrap that in a transaction? That is actually the purpose of this question.

CL. Over a year ago

Just how you would wrap any other statements. How is this ID related with transactions?

Bluebaron Over a year ago

My whole question is to avoid this method which is costing a lot of latency. This is how I have it now.

CL. Over a year ago

SQLite is an embedded database library; you cannot save time by sending multiple commands off to a server. Each command is executed synchronously inside your process. To optimize execution time, put more commands into one transaction.

Arkadia · Accepted Answer · 2013-06-05 15:58:52Z

0

Use the last_insert_rowid() function.

answered Jun 5, 2013 at 15:58

Arkadia

113 bronze badges

1 Comment

Bluebaron Over a year ago

By the second insert into bar I'd be getting ids from the table bar not foo.

Collectives™ on Stack Overflow

Insert into multiple tables SQLITE

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related