SQLite batch inserts to multiple tables

Question

This works fine:

insert into Genres (ID, Genre) 
select 1 AS ID, 'Action' AS Genre 
union select 2, 'Adventure'

...and this works fine:

insert into Movies_Genres (ID, MovieID, GenreID) 
select 1 AS ID, 2 AS MovieID, 1 AS GenreID 
union select 2, 3, 1

...but when I try to combine the two statements:

insert into Genres (ID, Genre) 
select 1 AS ID, 'Action' AS Genre 
union select 2, 'Adventure' 
insert into Movies_Genres (ID, MovieID, GenreID) 
select 1 AS ID, 2 AS MovieID, 1 AS GenreID 
union select 2, 3, 1

...it throws an error. I also tried separating the 2 table batches by a semicolon, but also got an error. "An error occured parsing the provided SQL statement." And "syntax error". I have a large amount of inserts to do, so I need to maximize performance, and I have found this approach very robust and fast:

How to insert multiple rows into a SQLite 3 table?

So, what is the proper syntax to batch together a bunch of inserts aimed at different tables?

How are you delivering the INSERT statements to the sqlite3? If you're doing that using a method that expects a single statement, you will not be able to include both INSERT statements in a single submission. — Larry Lustig
– Larry Lustig, Commented Oct 22, 2014 at 16:51

CL. · Accepted Answer · 2014-10-22 16:02:36Z

1

One statement cannot insert into multiple table.

Just execute multiple statement. (This will be fast if you use a single transaction.)

answered Oct 22, 2014 at 16:02

CL.

182k18 gold badges241 silver badges282 bronze badges

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

6 Comments

HerrimanCoder Over a year ago

sqlite doesn't have a batch separator (such as semicolon) for multiple statements? All other DB platforms support that, so I just figured sqlite would too.

CL. Over a year ago

It has, but the drivers in many languages/libraries do not allow it to prevent injection attacks.

HerrimanCoder Over a year ago

Got it working; just seems strange you can't batch up any insert you want, to any table, in the same batch.

TeeJay Over a year ago

@HerrimanCoder can you tell me please how you got it working i wanna insert into 2 tables

CL. Over a year ago

@TeeJay This depends on the language you're using. Ask a new question.

|

Collectives™ on Stack Overflow

SQLite batch inserts to multiple tables

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related