Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is it possible to insert multiple rows at a time in an SQLite database?

Tags:

syntax

sql

sqlite

In MySQL you can insert multiple rows like this:

INSERT INTO 'tablename' ('column1', 'column2') VALUES     ('data1', 'data2'),     ('data1', 'data2'),     ('data1', 'data2'),     ('data1', 'data2'); 

However, I am getting an error when I try to do something like this. Is it possible to insert multiple rows at a time in an SQLite database? What is the syntax to do that?

like image 305
Andrew Avatar asked Oct 22 '09 20:10

Andrew


People also ask

How do I insert multiple rows at a time in SQL?

Thus, we can use INSERT-SELECT-UNION query to insert data into multiple rows of the table. The SQL UNION query helps to select all the data that has been enclosed by the SELECT query through the INSERT statement.

Does SQLite support bulk insert?

SQLite doesn't have any special way to bulk insert data. To get optimal performance when inserting or updating data, ensure that you do the following: Use a transaction. Reuse the same parameterized command.

Which of the following method is used to insert multiple rows at a time in sqlite3 database?

The steps to insert multiple records to a table are: Prepare the connection to the database and then get a cursor. Get all your records to a list. Use executemany() method and pass the query and records list as arguments to the method.

Which of the following methods is used to insert multiple rows at a time in sqlite3 Python?

Python Insert multiple rows into SQLite table using the cursor's executemany()


2 Answers

update

As BrianCampbell points out here, SQLite 3.7.11 and above now supports the simpler syntax of the original post. However, the approach shown is still appropriate if you want maximum compatibility across legacy databases.

original answer

If I had privileges, I would bump river's reply: You can insert multiple rows in SQLite, you just need different syntax. To make it perfectly clear, the OPs MySQL example:

INSERT INTO 'tablename' ('column1', 'column2') VALUES   ('data1', 'data2'),   ('data1', 'data2'),   ('data1', 'data2'),   ('data1', 'data2'); 

This can be recast into SQLite as:

     INSERT INTO 'tablename'           SELECT 'data1' AS 'column1', 'data2' AS 'column2' UNION ALL SELECT 'data1', 'data2' UNION ALL SELECT 'data1', 'data2' UNION ALL SELECT 'data1', 'data2' 

a note on performance

I originally used this technique to efficiently load large datasets from Ruby on Rails. However, as Jaime Cook points out, it's not clear this is any faster wrapping individual INSERTs within a single transaction:

BEGIN TRANSACTION; INSERT INTO 'tablename' table VALUES ('data1', 'data2'); INSERT INTO 'tablename' table VALUES ('data3', 'data4'); ... COMMIT; 

If efficiency is your goal, you should try this first.

a note on UNION vs UNION ALL

As several people commented, if you use UNION ALL (as shown above), all rows will be inserted, so in this case, you'd get four rows of data1, data2. If you omit the ALL, then duplicate rows will be eliminated (and the operation will presumably be a bit slower). We're using UNION ALL since it more closely matches the semantics of the original post.

in closing

P.S.: Please +1 river's reply, as it presented the solution first.

like image 116
fearless_fool Avatar answered Sep 19 '22 05:09

fearless_fool


Yes it is possible, but not with the usual comma-separated insert values.

Try this...

insert into myTable (col1,col2)       select aValue as col1,anotherValue as col2       union select moreValue,evenMoreValue       union... 

Yes, it's a little ugly but easy enough to automate the generation of the statement from a set of values. Also, it appears you only need to declare the column names in the first select.

like image 34
river Avatar answered Sep 18 '22 05:09

river