2

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?

Community
  • 1
  • 1
HerrimanCoder
  • 6,835
  • 24
  • 78
  • 158
  • Use UNION ALL instead of UNION. – CL. Oct 22 '14 at 16:02
  • 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 Oct 22 '14 at 16:51

1 Answers1

1

One statement cannot insert into multiple table.

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

CL.
  • 173,858
  • 17
  • 217
  • 259